Welcome Guest, Not a member yet? Register   Sign In
MySql LIKE
#1

Hello everyone,

I am trying to do a sql query with LIKE:
PHP Code:
$this->db->query("SELECT * FROM pvp_map WHERE Server_Id = ".$Id." AND Name LIKE '%".$this->db->escape_like_str($Name)."%' ESCAPE '!' GROUP BY Player_Id, Name ORDER BY Name LIMIT ".$start.",".$limit); 

But when the string that is getting escaped starts with an ! it doesn't give any results.
This is a problem: !Joker!
This is NOT a problem: Joker!

Does anyone know how to fix this please?

I have to leave ESCAPE '!' in the query or else strings containing % or _ wont work.

The issue also happens when I do this:
PHP Code:
$this->db->where('Server_Id'$Id);
 
           $this->db->like('Name'$Name);
 
           $this->db->group_by(array("Player_Id""Name"));
 
           $this->db->order_by('Name');
 
           $this->db->from('pvp_map'); 
Reply
#2

Is there anyone that can help me with this issue? It is a real big problem for me.
Reply
#3

did you try to escape your search ?

!Joker! -> \!Joker\!
Reply
#4

What are the strings that are expected to match but don't?
Reply
#5

(02-17-2016, 08:34 AM)keulu Wrote: did you try to escape your search ?

!Joker! -> \!Joker\!

The problem is only when the string starts with an '!', if the '!' is in the middle of the string or the end there is no problem.

(02-17-2016, 09:19 AM)Narf Wrote: What are the strings that are expected to match but don't?

Can always be different, users can search for usernames.
At the moment I have 1.441.321 usernames saved in my database.

But usernames like these give issues:
! Astro !
!!!
!Buddla!
!l †r€d¡(¡
!2
!f:g:g
Reply
#6

(This post was last modified: 02-17-2016, 11:27 AM by Narf.)

Quote:mysql> create table test (dummy varchar(16) not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test(dummy) values ('!exclamation');
Query OK, 1 row affected (0.06 sec)

mysql> select dummy from test where dummy like '%!!%' escape '!';
+--------------+
| dummy |
+--------------+
| !exclamation |
+--------------+
1 row in set (0.00 sec)

It should work ...
Reply
#7

(02-17-2016, 11:27 AM)Narf Wrote:
Quote:mysql> create table test (dummy varchar(16) not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test(dummy) values ('!exclamation');
Query OK, 1 row affected (0.06 sec)

mysql> select dummy from test where dummy like '%!!%' escape '!';
+--------------+
| dummy        |
+--------------+
| !exclamation |
+--------------+
1 row in set (0.00 sec)

It should work ...

Over 1 million usernames and this query gives 0 result:
Code:
SELECT *
FROM `pvp_map`
WHERE `Name` LIKE '%!!%' ESCAPE '!'
GROUP BY `Player_Id`, `Name`
ORDER BY `Name`

I really don't know where the problem is.
Reply
#8

When I use this query (without escaping it):

Code:
SELECT *
FROM `pvp_map`
WHERE `Name` LIKE '%!%'
GROUP BY Player_Id, Name
ORDER BY Name


I get 3646 results.
Reply
#9

Well, that's really strange ... I don't know what could possibly cause this behavior.

Technically, you could change the escape character and work-around the issue that way, but I'm guessing that if you're allowing '!' in usernames, you'd allow everything else as well, and in that case this wouldn't help.
Reply
#10

(02-17-2016, 12:12 PM)Narf Wrote: Well, that's really strange ... I don't know what could possibly cause this behavior.

Technically, you could change the escape character and work-around the issue that way, but I'm guessing that if you're allowing '!' in usernames, you'd allow everything else as well, and in that case this wouldn't help.

Indeed, the problem is that I am scanning and gathering all users from an online game and I made a user search site.
The game lets people use every character available (even chinees and special chars). .

Every possible search works unless the string starts with '!'
I don't know what I can do to fix this, that is why I asked here.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB