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?

What does the data look like that you are querying? Can you make an example? — Pekka
– Pekka, Commented May 14, 2010 at 18:13
just to help you out, this is a mySQL query called via PHP, not a PHP query. A common mistake though! — WhiskeyTangoFoxtrot
– WhiskeyTangoFoxtrot, Commented May 14, 2010 at 18:25

Paolo · Accepted Answer · 2010-05-14 18:15:31Z

8

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')

answered May 14, 2010 at 18:15

Paolo

22.7k6 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitch Dempsey · Accepted Answer · 2010-05-14 18:14:12Z

2

It probably should be:

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

answered May 14, 2010 at 18:14

Mitch Dempsey

40.3k6 gold badges72 silver badges75 bronze badges

Comments

John Kugelman · Accepted Answer · 2010-05-14 18:23:35Z

2

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)";

answered May 14, 2010 at 18:23

John Kugelman

364k70 gold badges555 silver badges600 bronze badges

2 Comments

pg. Over a year ago

Wait, when I do this, I get an error message that the [] operator on this line: "$fruits[] = "'" . mysql_real_escape_string($fruit) . "'";" is not supported for strings.

John Kugelman Over a year ago

Careful if you edit the code, $fruits starts out as an array and turns into a string after the implode().

Vidar Vestnes · Accepted Answer · 2010-05-15 05:57:44Z

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

edited May 15, 2010 at 5:57

answered May 14, 2010 at 18:15

Vidar Vestnes

43k30 gold badges89 silver badges100 bronze badges

1 Comment

bobince Over a year ago

(Avoid double quotes for SQL string literal delimiters. The ANSI standard delimiter is the single quote; double quotes only work in MySQL, and then only for the default SQL_MODE setting.)

Collectives™ on Stack Overflow

Simple PHP query question: LIKE

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related