php mysql search in 2 columns in 2 tables

I have two tables in one DB, one called Cottages and one called Hotels. In both tables they have the same named fields.

I ba开发者_StackOverflow中文版sically have a search bar that i want it to search in both of the fields in both of the tables. (the two fields being called "Name" and "Location"

SO far I have

$sql = mysql_query("SELECT * FROM Cottages WHERE Name LIKE '%$term%' or Location LIKE '%$term%' LIMIT 0, 30");

But this only searches the Cottages table, how can I make it search both the cottages and hotel tables?

---

Would be better if you merge both tables in only one and add a new field like type (with values like cottage or hotel) to identiy each record.

That's called normalization and it's exactly what WordPress do when it save posts, categories, attachments and pages on the sabe database table.

---

TiuTalk's answer is right - if the columns are the same in both tables you should probably only use one table for this data, and add a type column.

In addition using LIKE '%foo%' is slow. You should look into full text search. It also handles multiple tables in the same way as with LIKE plus you can sort by relevance. When sorting by relevance you get the most relevant rows first regardless of which table they came from.

If you can't change your design and you want to get exactly half the results from each table you can query each separately and use UNION ALL to combine the results:

(SELECT * FROM Cottages WHERE Name LIKE '%$term%' or Location LIKE '%$term%' LIMIT 15)
UNION ALL
(SELECT * FROM Hotels WHERE Name LIKE '%$term%' or Location LIKE '%$term%' LIMIT 15)

Obviously the columns must be the same in both tables for this to work. Don't use SELECT * though - you should explicitly list the column names otherwise reordering the columns could cause this query to break.