Query find value in comma seperated list mysql

Question

in my script a "restaurant" can have multiple locations so, i made a column in the restaurant table containing a coma seperated list with locations.

Now i want to make a msql query that checks if the id can be fount is this column (comma seperated list) and if so then select it. i came up with this

SELECT restaurant_id,restaurant_name 
FROM restaurant WHERE ('.$locIdList.') IN (locationRes) 
ORDER BY restaurant_name ASC'

It does work... but i have some restaurants where I added location 16 and 17 so (16,17) now when i do this query for location 16 it shows the restaurant but when i dot this for location 17 it does not... but the whole point was to get the multi values from the comma seperated list.

So how to do this ?

look at the "LIKE"-Keyword. And also for http://en.wikipedia.org/wiki/First_normal_form — EGOrecords, Jul 13 '14 at 18:53
It would be best if you normalized your schema. Doing this with comma-separated lists is difficult and inefficient. Make a separate table of restaurant locations. — Barmar, Jul 13 '14 at 18:54

score 1 · Answer 1 · edited Apr 28 '15 at 19:47

You can use PHP to generate the query for each comma-delimited value. i.e., run a PHP loop on comma-delimited comparison string, convert it into individual items and compare each item through LIKE Operator and an IN () function.

SELECT restaurant_id,restaurant_name 
FROM restaurant WHERE ('16') IN (locationRes) 
OR 
FROM restaurant WHERE ('17') IN (locationRes) 
ORDER BY restaurant_name ASC'

score 0 · Answer 2 · answered Jul 13 '14 at 18:55

0

You should avoid putting multiple values inside a single column.

Instead, it's recommended to create another table locations(location_id, col1, col2, restaurant_id), while the restaurant_id field references to the primary key in table restaurant.

answered Jul 13 '14 at 18:55

Chris Lam

3,526
13
10

Tim · Answer 3 · 2014-07-13T19:05:43.660

If you have a comma separated list you are pulling from a database, you could use PHP to separate the list and create an array of each item.

$result_of_sql = "restaurant 1, restaurant 2, restaurant 3, restaurant 4, restaurant 5, restaurant 6";

$restaurants = explode(',', $result_of_sql);

echo '<ul>';
foreach ($restaurants as $restaurant) {
  echo '<li>' . trim($restaurant) . '</li>';
}
echo '</ul>';

What happens here is first, you pull out all of you restaurants (the comma separated list). Then you use explode to take away the commas and create an array. Then you use the foreach loop to echo the entire array out. trim is just to clean everything up by removing whitespace you might have before and after the restaurant name.

score 0 · Accepted Answer · edited May 23 '17 at 12:21

The best solution would be to create a relation table that implements the many-to-many relationship between restaurants and locations. Then you can use a solution like How to return rows that have the same column values in MySql to find all the restaurants that are in all locations.

To search for a value in a comma-separated list, you use FIND_IN_SET. But this can only search for one value at a time. If you want to find restaurants that are in all locations, you need to combine multiple calls:

$locArray = explode(',', $locIdList);
$locQuery = implode(' AND ', array_map(function($loc) { return "FIND_IN_SET($loc, locationRes)"; }, $locArray));
$query = "SELECT restaurant_id,restaurant_name 
          FROM restaurant 
          WHERE $locQuery
          ORDER BY restaurant_name ASC";

If you want to find restaurants that are in any of the locations instead of all locations, change AND to OR.

Query find value in comma seperated list mysql

4 Answers4