[SOLVED] (DB HELP) Merging 2 tables with a select query?

[matty]

Hi,

 

I have two tables in my database.

One is called 'hotfood', the other is 'coldfood'. What I want to do is have a single SELECT query for both of these tables. This is so I can then use 'mysql_num_rows' on this query, like so.

 

if(mysql_num_rows($id)==0)
{
print "Food type does not exsist";
}
else
{
--
}

 

It basicaly to do a search type thing, the above checks that the food name exists by checking both the hot and cold food table, therefore I need to two merged into the select query so that I can search it.

 

Get me?

Sorry for the poor explanation.

 

Thanks in advance,

Matt

[Psycho]

You could create a merge table: http://www.jeff-barr.com/?p=110 where the two tables would exist as a virtual combined table.

 

But, I would suggest creating just one table and adding a column to identify wether the food is hot, cold, or both - instead of having two tables.

[Barand]

SELECT name, 'hot' AS foodtype FROM hotfood WHERE name LIKE '%$search%'
UNION
SELECT name, 'cold' AS foodtype FROM coldfood WHERE name LIKE '%$search%'

[matty]

Thanks Barand, that worked perfect