query specific table based on search keyword in mysql

Question

I am creating a search portal in PHP from which user can search for a specific cuisine. In MySQL I have multiple tables for each cuisine and the respective hotel names that offer the cuisine. For example, in table

How can I query a specific cuisine table based on the cuisine search keyword?

So if a user enters 'mexican' as the search query, how can it connect to the 'Table2 - Mexican' and return the hotel names from this table?

Table1 - Chinese
_______________________
| id   |   hotelname  |
|______|______________|
| 1    |  hotel1      |
| 2    |  hotel2      |
| 3    |  hotel3      |
| 4    |  hotel4      |
| 5    |  hotel5      |
|______|______________|

Table2 - Mexican
_______________________
| id   |   hotelname  |
|______|______________|
| 1    |  hotel1      |
| 2    |  hotel2      |
| 3    |  hotel3      |
| 4    |  hotel4      |
| 5    |  hotel5      |
|______|______________|

Table3 - Pizza
_______________________
| id   |   hotelname  |
|______|______________|
| 1    |  hotel1      |
| 2    |  hotel2      |
| 3    |  hotel3      |
| 4    |  hotel4      |
| 5    |  hotel5      |
|______|______________|

A better design would be **a single table** named `hotel_food` and the columns `hotel` and `food_type` for instance. — juergen d, Jun 05 '14 at 11:01

dulange · Answer 1 · 2014-06-05T11:22:35.190

Your database concept is very unflexible. I think you should put the cuisines into your database as information (i.e. table content) instead of metadata describing single tables. Tables should generally considered to be static just like the code you write to access the database and its tables. If you implement the cuisines as different tables you would have to hardwire every cuisine into your code.

Here is a suggestion for a better approach:

Create a hotels table to store all the hotels,
Create a cuisines table to store all the different types of cuisines,
Make an additional table to establish the n:m relationship between the hotel and the cuisine.

Example:

hotels:  id, name, address, city, telno, email
cuisine: id, name, description
rel:     cuisine, hotel (where both are the foreign keys to the
                         id columns of the respective tables above)

See also:

score 0 · Answer 2 · answered Jun 05 '14 at 11:15

you can check SQL UNION. But instead of having multiple tables with the same fields, you can try normalization to minimize the redundancy and to make queries easier.

Something like:

 Hotel Table
 -----------------------------
 id | hotelname    | categoryID
 ------------------------------
 1  | hotel name 1 | 1
 2  | hotel name 2 | 2
 -----------------------------

 Category Table
 -------------------
  id | categoryname 
 -------------------
   1 | chinese
   2 | mexican
 ------------------

And query as simple as:

SELECT a.hotelname, b,categoryname 
      FROM hotel_table a 
      LEFT JOIN category_table b 
      ON a.categoryID = b.id AND b.categoryname LIKE '%mexican%';

score 0 · Accepted Answer · edited May 23 '17 at 11:49

You might want to check this question to create a many-to-many relationship:
many-to-many and many-to-many intersections

I guess what you would like to achieve is something like this:

Table1 - Hotel
_______________________
| id   |   hotelname  |
|______|______________|
| 1    |  hotel1      |
| 2    |  hotel2      |
| 3    |  hotel3      |
| 4    |  hotel4      |
| 5    |  hotel5      |
|______|______________|

Table2 - Cuisine
____________________________________________
| id   | cuisine_name | keywords           |
|______|______________|____________________|
| 1    |  Chinese     | Shandong,Noodles,. |
| 2    |  Mexican     | Tacos,Beans,...    |
| 3    |  Itarian     | Pizza,Pasta,..     |
|______|______________|____________________|

Table3 - HotelCuisine
___________________________________
| id   |  hotel_id  |  cuisine_id |
|______|____________|______________
| 1    |    1       |      2      |
| 2    |    1       |      3      |
| 3    |    2       |      1      |
| 4    |    2       |      2      |
| 5    |    3       |      3      |
|______|____________|_____________|

SQL:

 SELECT hotelname, cuisine_name FROM Hotel
 INNER JOIN HotelCuisine ON Hotel.id = HotelCuisine.hotel_id
 INNER JOIN Cuisine ON Cuisine.id = HotelCuisine.cuisine_id
 WHERE keywords like '%pizza%'

Result:

________________________________________
| hotelname     | cuisine_name         |
|_______________|______________________|
| hotel1        | Itarian              |                     
| hotel3        | Itarian              |                     
|_______________|______________________|

DEMO: http://sqlfiddle.com/#!2/961de/1

Hope this helps

Thank you for the suggestion. I got it working successfully displaying the search results. Now I found out something of a loophole with the code. If I enter '%' in the search field it is returning all the data from the tables. How can I make it specific to the keyword entered? — kay5, Jun 10 '14 at 05:07
@user3710725, thanks. I guess you might want to use something like `mysql_real_escape_string` to escape `%` and other special characters. You can check here http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — naota, Jun 10 '14 at 05:56

query specific table based on search keyword in mysql

3 Answers3