Member Avatar for ajuv_001

Hi friends,
I am planning to develop a local hotel search engine. ive prepared the dbmodel and i've attached it with this thread. please help me to write search queries for searching hotel.
there are 11 tebles country,city,state,block,street,landmark,address,hotel,rooms,and amenities .
please go through the model i've attached and help me to write search queries for search hotel by city,street,block and hotel. after executing search query the result should contain values from every tables.every tables are linked through foreign key. i've tried but iam totally confused and i am not getting proper query...please help me......
thanks
Aju

hoteldb_model.png 54.04 KB
  • 2 Contributors
  • 3 Replies
  • 120 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by ajuv_001

Dani AI

Generated

Good move following and breaking the task into smaller queries. The usual, reliable approach is: 1) build a base result that joins hotel -> address -> street/block -> city -> state -> country, 2) add amenities via a many-to-many mapping, and 3) add search filters (city / street / block / hotel) one at a time and test each step. later reported a working query; the templates below show how to combine those pieces cleanly and how to keep performance and correctness in mind.

Example template (adjust column and FK names to match the model):

SELECT
  h.id, h.name AS hotel_name,
  a.id AS address_id, s.name AS street, b.name AS block,
  c.name AS city, st.name AS state, cn.name AS country,
  GROUP_CONCAT(DISTINCT am.name SEPARATOR ', ') AS amenities
FROM hotel h
LEFT JOIN address a ON a.hotel_id = h.id
LEFT JOIN street s ON s.id = a.street_id
LEFT JOIN block b ON b.id = a.block_id
LEFT JOIN city c ON c.id = a.city_id
LEFT JOIN state st ON st.id = c.state_id
LEFT JOIN country cn ON cn.id = st.country_id
LEFT JOIN hotel_amenities ha ON ha.hotel_id = h.id
LEFT JOIN amenities am ON am.id = ha.amenity_id
WHERE c.name = 'TargetCity' AND s.name LIKE '%Main St%'
GROUP BY h.id;

Notes and practical tips: prefer LEFT JOIN when some address pieces may be missing; use INNER JOIN when those relationships must exist. For text searches on hotel name/description, consider MATCH(...) AGAINST(...) with a FULLTEXT index instead of many LIKE patterns. Always index FK columns used in joins and columns used in WHERE clauses. Avoid SELECT *; list only needed columns. Use prepared statements or parameterized queries to prevent SQL injection, and test performance with EXPLAIN.

Useful references: MySQL JOIN documentation and MySQL full-text search.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

Start with writing smaller queries that solve part of the problem. Later on try to combine them to get a more complex result. If you have specific questions, post your query/queries and trouble/error here.

Member Avatar for ajuv_001

thanx....i will try like this and let u know.....

Member Avatar for ajuv_001

dude....i 've written the search query that is working tooo.....thx yaar...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.