+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: MySql LIKE (/showthread.php?tid=64390)
Pages:
1
2
MySql LIKE - glennm - 02-15-2016
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');
RE: MySql LIKE - glennm - 02-17-2016
Is there anyone that can help me with this issue? It is a real big problem for me.
RE: MySql LIKE - keulu - 02-17-2016
did you try to escape your search ?
!Joker! -> \!Joker\!
RE: MySql LIKE - Narf - 02-17-2016
What are the strings that are expected to match but don't?
RE: MySql LIKE - glennm - 02-17-2016
(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
RE: MySql LIKE - Narf - 02-17-2016
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 ...
RE: MySql LIKE - glennm - 02-17-2016
(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.
RE: MySql LIKE - glennm - 02-17-2016
When I use this query (without escaping it):
Code:
SELECT *
FROM `pvp_map`
WHERE `Name` LIKE '%!%'
GROUP BY Player_Id, Name
ORDER BY NameI get 3646 results.
RE: MySql LIKE - Narf - 02-17-2016
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.
RE: MySql LIKE - glennm - 02-17-2016
(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.