I have a value "Berlin, Paris, London, ..." and I Want to do an MySQL Query
SELECT * FROM restaurants
WHERE restaurant_city LIKE '%array%';
To get all restaurants where the city contains EITHER Berlin OR Paris OR London
How can I realize that?
-
where does the array come from and in which format?Ello– Ello2015-07-29 11:46:12 +00:00Commented Jul 29, 2015 at 11:46
-
SELECT * FROM restaurants WHERE restaurant_city IN ('Berlin','Paris','London');Strawberry– Strawberry2015-07-29 11:54:47 +00:00Commented Jul 29, 2015 at 11:54
-
1@Strawberry earlier I also understood it but Moskau wants to pass array instead of cities.....Zafar Malik– Zafar Malik2015-07-29 12:00:13 +00:00Commented Jul 29, 2015 at 12:00
-
You have to implode the array and passed it to query using REGEXP. Look at the answer belowSantosh Jagtap– Santosh Jagtap2015-07-29 12:01:48 +00:00Commented Jul 29, 2015 at 12:01
-
@SantoshJagtap: OP doesn't have to. There are several tricks for this. We can also get it done from sql itself.Raging Bull– Raging Bull2015-07-29 12:03:51 +00:00Commented Jul 29, 2015 at 12:03
Add a comment
|
5 Answers
If in mysql we can do
SELECT * from restaurants where restaurant_city REGEXP 'Berlin|Paris|London';
Implode the given array using '|' and then use this generated string in query (In php).
$comma_separated = implode("|", $array);
Use the comma separated string in query
4 Comments
alagu
Santhosh I think he is not mentioned PHP.
Santosh Jagtap
@kuttyraj Yes he not mentioned PHP. I edited my answer.
Ravinder Reddy
@kuttyraj: This question is tagged
mysqli, Can we use mysqli without PHP ?alagu
@RavinderReddy From his question, we might know that he is expecting sql result. Not PHP result. And It is not harder to do with normal SQL script. ;-)
Do a reverse trick.
SELECT * FROM restaurants WHERE 'array' LIKE concat('%',restaurant_city,'%')
So the query will be like:
SELECT * FROM Table1 WHERE 'Berlin, Paris, London' LIKE concat('%',restaurant_city,'%')
Sample in SQL Fiddle
Comments
Easiest way:
This is working fine.
SELECT * FROM restaurants WHERE
restaurant_city LIKE '%Berlin%' OR
restaurant_city LIKE '%Paris%' OR
restaurant_city LIKE '%London%';
Or else, you could do same how Raging Bull mentioned.
See local sample test.
Comments
Try this
SELECT * FROM restaurants WHERE ( restaurant_city LIKE '%Berlin%' OR restaurant_city LIKE '%London%')
Worked on my case ...
Comments
Try it-
SELECT * FROM restaurants
WHERE find_in_set (restaurant_city,'Berlin,Paris,London');
2 Comments
Zafar Malik
I have edited my query as per understanding with problem, so please review your -ve voting.
Zafar Malik
who ever doing -ve voting should update his/her remarks....what is wrong here in this query.