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 ( stackoverflow.com/questions/471914/… ). You would be better creating a third table to do lookups - one that holds the restaurant id and the types — Professor Abronsius
– Professor Abronsius, Commented Oct 12, 2015 at 8: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
– Narendrasingh Sisodia, Commented Oct 12, 2015 at 8:48

mTorres · Accepted Answer · 2015-10-12 08:05:05Z

1

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

answered Oct 12, 2015 at 8:05

mTorres

3,5902 gold badges28 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Sql + php join and explode

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related