MySql : can i query " WHERE '$str' LIKE %table.col% " ?

Question

Basically i want to add wildcards to the the col value when searching...

Usually I do this the other way around like this:

WHERE cakes.cake_name LIKE '%$cake_search%'

however now i want it to match the inverse:

the user searches for 'treacle sponge', i want this to match a row where the cake_name column = 'sponge'.

is this possible?

Answer 1

WHERE '$cake_search' LIKE  concat('%',cakes.cake_name, '%')

should work. It will need a full table scan but so will the inverse query. Have you looked into full text search for MySQL? It will likely make this sort of query more efficient.

Answer 2

You would have to split the user supplied input on the space character and dynamically construct your query to check the column for those values:

$input = "treacle sponge";
$input_words = explode(' ', $input);

$sql_where = "WHERE cakes.cake_name IN('" . implode("','", $input_words) . "')"; // generates: WHERE cakes.cake_name IN('treacle','sponge')

Answer 3

Why not using MATCH?

MATCH(`cake_name`) AGAINST ('treacle sponge')

Answer 4

In order to prevent SQL-Injection, I suggest using prepared statements.

$prepStmt = $conn->prepare('SELECT ... WHERE cakes.cake_name LIKE :cake_search
');
if($prepStmt->execute(array('cake_search'=>"%$cake_search%"))) {
 ...
}

Or, using full text search:

$prepStmt = $conn->prepare('SELECT ... WHERE MATCH (`cake_name`) AGAINST (:cake_search IN BOOLEAN MODE)');
if($prepStmt->execute(array('cake_search'=>$cake_search_words))) {
 ...
}

See http://stackoverflow.com/questions/3238855/json-specialchars-json-php-5-2-13 for a complete example.. ;)