PHP MYSQL Search Syntax using LIKE.

Php Mysql

[asevie]

This was pulled from another post in here. I am trying to do a LIKE search similar to this;

SELECT JokeText FROM Jokes WHERE JokeText LIKE "%chicken%";

But instead of hardcoding 'chickens' I'd like to use a variable. My question is, how do I get the % sign so that it is parsed with the variable? This is what I have so far, minus the '%'...

$sql = "SELECT name FROM CardSets WHERE name LIKE $textfield3";

Thanks...

[rocknbil]

Welcome aboard asevie, you change your quoting scheme,

$sql = "SELECT name FROM CardSets WHERE name LIKE '%$textfield3%'";

or escape the inner quotes.

$sql = "SELECT name FROM CardSets WHERE name LIKE \"%$textfield3%\"";

Care must be taken to manage any quotes that might be inside the term. Single quoted where:

$term = "chicken's";

$term = preg_replace("'","''",$term);
(double-quote where values)

$sql = "SELECT name FROM CardSets WHERE name LIKE '%$term%'";

You might not be able to see it per the formatting on this page - the replacement in the preg_replace() above is double quote, followed by two single quotes, followed by a double quote. The idea is if your term is encapsulated in single quotes, you need to double up any single quotes within the term itself so mySQL will interpret it correctly.

Double quoted where, same concept:

$term = 'the "red" chickens';

$term = preg_replace('"','""',$term);

$sql = "SELECT name FROM CardSets WHERE name LIKE \"%$term%\"";

Hmm, looking at that last one you may even have to escape the internal double quotes on preg_replace() to satisfy PHP - the single quoting might be less work . . .

[asevie]

Thanks for the welcome and the response. This is my first project with PHP, I've been using a great book but the quote, double quotes, escapes etc will take some time to digest. Thanks again, I'll respond if I have any problems!