Simple PHP query question: LIKE

Question

When I replace

$ordering = "apples, bananas, cranberries, grapes";

with

$ordering = "apples, bananas, grapes";

I no longer want cranberries to be returned by my query, which I've written out like this:

$query = "SELECT * from dbname where FruitName LIKE '$ordering'";

Of Course this doesn't work, because I used LIKE wrong. I've read through various manuals that describe how to use LIKE and it doesn't quite make sense to me.

If I change the end of the db to "LIKE "apples"" that works for limiting it to just apples. Do I have to explode the ordering on the ", " or is there a way to do this in the query?

Answer 1

It probably should be:

SELECT * FROM database WHERE FruitName IN ('apples','bananas','grapes')

Answer 2

try using

SELECT * from dbname WHERE FruitName IN ('apples','bananas','grapes')

if you need the result to be in the same order as the IN list extend the query with and ORDER BY

SELECT * from dbname WHERE FruitName IN ('apples','bananas','grapes') ORDER BY FruitName

Answer 3

LIKE is normally used for partially matching strings, e.g. you'd use

WHERE fruitname LIKE 'app%' 

to find 'apple' and 'apples'.

What you (probably) want is the IN clause, e.g.

WHERE fruitname IN ('apples', 'bananas', 'grapes')

Answer 4

You need to explode the string and convert it to the appropriate SQL. This is probably the SQL syntax you want to end up with:

SELECT * FROM dbname WHERE FruitName IN ('apples', 'bananas', 'grapes')

PHP code:

$fruits = array();

foreach (explode(', ', $ordering) as $fruit) {
    $fruits[] = "'" . mysql_real_escape_string($fruit) . "'";
}

$fruits = implode(', ', $fruits);
$sql = "SELECT * FROM dbname WHERE FruitName IN ($fruits)";