multiple tables, not working :(

Anon

Hello, I am trying to search across all tables in a database for a certain field.

I've tried this and it returns nothing. 🙁

Please help!
(PHP Replaces $COMPANY with the search keyword)

SELECT
africa.COMPANY_NAME as a,
emirates.COMPANY_NAME as e,
gulf.COMPANY_NAME as g,
meast.COMPANY_NAME as m,
pacific.COMPANY_NAME as p,
saudi.COMPANY_NAME as g

FROM
africa, emirates, gulf, meast, pacific, saudi

WHERE
africa.COMPANY_NAME like '%$COMPANY%' OR
emirates.COMPANY_NAME like '%$COMPANY%' OR
gulf.COMPANY_NAME like '%$COMPANY%' OR
meast.COMPANY_NAME like '%$COMPANY%' OR
pacific.COMPANY_NAME like '%$COMPANY%' OR
saudi.COMPANY_NAME like '%$COMPANY%'

calevans

You need to link the tables together in your where clause.

Select table1.col1,
table2.col1
from table1, table2
where table1.pk = table2.fk

In your case though, this looks more like a database that is not designed to answer this question. If you have cities from the various regions around the globe in separate tables, if the table structures are all the same then I would suggest that you gang them all into 1 table:

company

companyID (auto_increment bigint)

COMPANY_NAME

REGION

Then you would simply search:

Select *
from company where COMPANY_NAME = '%$COMPANY%'

HTH,

Cal

Cal Evans
Senior Internet Dreamer
http://www.calevans.com
*

Anon

Well I need the tables seperate, thanks for the reply but I've just re-wrote the PHP Code.

now it's

$regions=array("africa","meast",....)

foreach ($regions as $region){

/ SQL Stuff Here /

}

🙂