mysql like values with comma

Question

Basically, the setup of the table is something like this:

+-----------+----------+-------------+
|   land    |   city   |  perimeter  |
+-----------+----------+-------------+
|  America  |  Kansas  |      1      |
|  Britain  |  Berlin  |      4      |
|  Japan    |  Tokyo   |      5      |
+-----------+----------+-------------+

My current query:

$query = "SELECT land, city, perimeter FROM agents WHERE land LIKE ? OR city LIKE ? OR perimeter LIKE ?";
$params = array("%China%","%Kansas%","%6%");

This query works, with this it will return America,Kansas,1. However if my params will be equal to:

$params = array("%China%","%Beijing,London,Kansas,Bali%","%6%");

This will not return anything. How can I use LIKE in values separated with commas to match at least if one item exists in those comma separated values.

See here: http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — ceving, Jul 26 '12 at 12:23

score 1 · Accepted Answer · answered Jul 26 '12 at 12:27

1

Instead of LIKE use REGEXP (more information here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html).

$query = "SELECT land, city, perimeter FROM agents
   WHERE land REGEXP ? OR city REGEXP ? OR perimeter REGEXP ?";
$params = array("^China$","^Beijing|London|Kansas|Bali$","^6$");

answered Jul 26 '12 at 12:27

Kuba Wyrostek

6,163
1
22
40

What if I have the other way around, the database has the comma separated values then the value on params array is just a single word, will this approach fit? – Leandro Garcia Jul 30 '12 at 07:55
yes, except in situation where the word you look for is a substring of another (i.e. you are looking for "York" and there is "New York" in database). to get around this case - include separator character in search. – Kuba Wyrostek Jul 30 '12 at 08:39
Badly need your help amigo, this doesn't work if my `city` on database is `Beijing,London,Kansas,Bali` then in my parameter `"^Beijing|Jakarta$"`. What do I need to adjust? Thanks. – Leandro Garcia Jul 30 '12 at 11:48
Remove ^ and $ as these matches begin-of-line and end-of-line respectively. Let it simply be `Beijing|Jakarta`. – Kuba Wyrostek Jul 30 '12 at 12:00
If you want to avoid the "substring problem" I mentioned before you must include separator in search, like this: `,Beijing,|,Jakarta,` and change condition to: `concat(',',city,',') regexp ?`. – Kuba Wyrostek Jul 30 '12 at 12:02
No problem. I'll message you when I will be looking for some cheap apartment in Spain next summer. ;] – Kuba Wyrostek Jul 30 '12 at 12:44
don't worry; just kidding. :] – Kuba Wyrostek Jul 30 '12 at 15:39

score 0 · Answer 2 · answered Jul 26 '12 at 12:29

You can't pass comma separated values and expect them to be grouped properly by the parameter function.

Your query as it stands returns:

SELECT land, city, perimeter FROM agents WHERE land LIKE 'China' OR city LIKE 'Beijing,London,Kansas,Bali' OR perimeter LIKE '6'

Which is clearly not what you want.

What you want to do will need to check if any of the parameters is an explodable array through something like if(count(explode(',', $myArray))>1) to see if any of the variables passed to the query are arrays containing multiple fields. If they are, you want to run a different query string that checks for multiple or statements properly.

mysql like values with comma

2 Answers2