CODEWITHSUNDEEP
phpmysql
Rajasekar
Rajasekar

Reputation: 18978

How to construct the query

I am confused with building the query. I am implementing the search feature in a restaurant site. User first selects the area he is searching for restaurant and then he select the food types like chinese, japanese, thai from the checkboxes.

After selecting all these, the restaurants which providing the selected food in the selected area will be displayed. I am succeeded in getting the pincodes and food types from the yser.

I hav two table with the following fields,

restaurant_dp table with fields

id  -ID of the restaurant
pcode -pincode of the area

restaurant_iw table with fields

id  - id of the restaurant
menu  - menu the restaurant provides (eg., Chinese, thai etc.,)

My confusion is how to fetch the records from both the tables with the conditions:

Plz help. Any help will be appreciated

Upvotes: 0

Views: 72

Answers (5)

You
You

Reputation: 23824

You really should merge these to one table, with all fields relating to a certain restaurant in the same row. If you do that, the query simply becomes SELECT * FROM restaurants WHERE pcode = 'code' AND menu = 'menu';. Your current setup makes things more difficult, requiring a join:

SELECT * FROM restaurant_iw AS iw
LEFT JOIN restaurant_dp AS dp ON iw.id = dp.id
WHERE dp.pcode = 'code' AND iw.menu = 'menu';

Upvotes: 0

Dylan West
Dylan West

Reputation: 602

select * from restaurant_dp dp join restaurant_iw iw on dp.pcode = iw.pcode where dp.pcode = $userselectedpincode and iw.menu = $userselectedmenu;

then you just have to make sure that the two variables used in the query are populated properly.

Upvotes: 0

David
David

Reputation: 219057

SELECT DISTINCT dp.id FROM restaurant_dp dp INNER JOIN restaurant_iw iw ON dp.id = iw.id WHERE dp.pcode = $pcode AND iw.menu = $menu

Upvotes: 1

Kyra
Kyra

Reputation: 5417

SELECT * 
FROM restaurant_dp AS dp
LEFT JOIN restaurant_iw AS iw
ON dp.IDrestaurant= iw.IDrestaurant
WHERE dp.pcode = Userselectedpincode 
AND iw.menu = userselectedmenu

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

select dp.id
from restaurant_dp dp
inner join restaurant_iw iw on dp.id = iw.id
where dp.pcode = Userselectedpincode 
    and iw.menu = userselectedmenu

Upvotes: 4

Related Questions