Sql + php join and explode

Question

I have 2 tables on my mysql database : - restaurant - restaurant_type

restaurant has 3 column : id, idtype, name

Example :

1, 1-2, Restaurant name

2, 2-3-5, Restaurant name

restaurant_type has 2 column : idtype, typename

Example :

Salad

Burger

Steak house

Japanese

French fries

For that moment, my sql request is :

mysql_query("select distinct 
                restaurant.idtype,
                restaurant_type.idtype,
                restaurant_type.typename 
                from restaurant,restaurant_type 
                where restaurant.idtype=restaurant_type.idtype 
                order by restaurant_type.typename");

But I don't know how can I add explode function to search different idtype.

An idea to help me ?

Thank you !

There is no equivalent of `explode` in mysql and other solutions for attempting similar are, to be honest, very messy ( http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query ). You would be better creating a third table to do lookups - one that holds the restaurant id and the types — Professor Abronsius, Oct 12 '15 at 08:00
If you store comma separated values within your `restaurant` tables column `idtype` than you can simply use `FIND_IN_SET` function of MySQL — Narendrasingh Sisodia, Oct 12 '15 at 08:48

score 1 · Accepted Answer · answered Oct 12 '15 at 08:05

Your table scheme is wrong. You're treating a N:M relation (restaurant <--> Type) as 1:N which is not. So instead of 2 tables, you need 3:

restaurant(id, name)
type(id, name)
restaurant_type(id_restaurant, id_type)

Example data:

Restaurant
==========
1, restaurant name 1
2, restaurant name 2

Type
====
1, Salad
2, Burguer
3, Steak House
4, Japanese
5, French Fries

Restaurant type
===============
1, 1
1, 2
2, 2 
2, 3
2, 5

And then your query:

SELECT DISTINCT restaurant.name, type.name
FROM restaurant,restaurant_type 
WHERE restaurant.id = restaurant_type.id_restaurant and restaurant_type.id_type = type.id
ORDER BY type.name

Sql + php join and explode

1 Answers1