Geek Answers Handbook

Mysql query problems using LIKE and apostrophe

So, I have an interesting problem that I have never encountered and cannot find a lot of information about fixing the problem. I have a massive database in which there is a huge amount of data (10 years) and an attempt to search it.

Now the search stuff works fine, but recently someone drew my attention to the “error” if you did. I tried to solve the problem to get the expected results, but to no avail.

That's my problem:

When someone uses an apostrophe in a search, this does not mean that the search is faulty, but it does not return any results. For example, when searching for Pete's , a query is executed but returns nothing. Yes, I'm sure the query has mysql_real_escape_string (), so Pete's becomes Pete\'s .

Even when I try to query it using the phpmysql search function, I get weird results. The request should look like this:

 SELECT * FROM `smd_article` WHERE `copy` LIKE '%Pete\'s%'

But when I use the search function in phpmyadmin, it gives me:

 SELECT * FROM `smd_article` WHERE `copy` LIKE '%Pete''s%'

And when I actually type the query in the sql tab, it still doesn't return any results. But there are some 17K records that come back when I just use Pete and some 3k records when I just write.

So I'm curious what I'm doing wrong or not enough to make it so that it can use the apostrophe in the query using the LIKE statement. Thoughts?

+6
source share
2 answers

try mysql_real_escape_string

 LIKE '%" . mysql_real_escape_string($find) . "%'

or

  LIKE '%Pete%27s%'

or if you use pdo, you can do, for example,

 $search ='Pete\'s' $stmt = $db->prepare("SELECT * FROM `smd_article` WHERE `copy` LIKE ?"); $stmt->bindValue(1, "%$search%", PDO::PARAM_STR); $stmt->execute();
+5
source

The problem is not because of the single quote. ' You escaped it correctly. (as a search function in phpmyadmin):

String: Pete

MySQL: 'Pete''s'

So the following query will work. (I tested it)

 SELECT * FROM `smd_article` WHERE copy LIKE '%Pete''s%'

You can also see the IN instruction:

 SELECT * FROM `smd_article` WHERE copy IN ( 'Pete', 'Pete''s' )
+1
source

More articles:


All Articles