Search multiple tables

I had four different tables say table1, table2, table3, and table4 and this tables contain different data; now I want to do a search on this 4 table that will come up with one result.

table1-fields: 
id, product_name, price, product_category; 
table2-fields: 
id,company_name, company_description, location; 
table3-fields:
id, business_name, location, business_category;
table4-fields:
id, building_type, location, name

I had this form:

<form name="me" action="search.php"> 
<input type="text" name="search" style="width:inherit;" size="25"  
value="type your search here" 
onclick="me.search.value='';" style="font-size:small" height="20"/>
<input type="submit" name="Submit2" class="button" value="SEARCH"  />
</form>

In search.php I want some开发者_StackOverflowthing like

SELECT * FROM table1, table2, table3, table4 
WHERE table1.product_name LIKE '%" . $name .  "%' 
OR table2.company_name LIKE '%" . $name .  "%' 
OR table3.business_name LIKE '%" . $name .  "%' 
OR table4.name LIKE '%" . $name .  "%'`; 

a sql statement that will search these 4 tables at the same time.

How could I do this? Please help.

---

If there is a common element, like there's an ID or other identifier which is shared between each table, you can use a JOIN. This'll allow you to select rows based on data contained in each table.

You'd need to provide more information about your tables and what you're searching for for me to be able to provide a full example.

---

Write a query for each table that returns the result in the same columns in each query; use UNION to joint them into 1 result set:

(SELECT id, name from table1 where name='bob')
UNION
(SELECT moar_id as id, nickname as name from table2 WHERE nickname='bob')
UNION
(SELECT account_id as id, fullname as name from table2 WHERE fullname='bob')

http://dev.mysql.com/doc/refman/5.0/en/union.html