PHP / SQL Using data from previous query to query another table

Question

Hi,

I've been trying to work this our for a while, but having trouble.

We have 4 tables consiting of, suppliers, supplier_areas, supplier_languages and supplier_products options.

I am trying to make an advanced search where users can search members using any of the above.

An example search may be all suppliers in a certain area that speak english & french and also sell products 1 and 2.

I know the locations table will always have to be queried first, followed by the languages, then the products table, and finally by specific fields out of the suppliers table.

E.g.

All supplierid's from supplier_areas where locationid = 1

This for example returns an array with the supplierids '1', '5', '10'

I then need to query the languages table to find out which of these suppliers speak english which the only statement I could see using is

SELECT supplierid from supplier_languages WHERE languageid = 1 OR languageid = 2 AND supplierid = 1 OR supplierid = 5 OR supplierid = 10

Then obviously use the result from taht to query the final two tables.

I'm assuming the OR statement that i'm planning on doing will be too slow and server intensive. The results returned from the first query could be anything upto 200+ supplier ids.

Any help would be appreciated.

Thanks

Answer 1

You should look into using JOIN in your mysql statements.

Answer 2

you can combine all of the queries into one:

select * 
from supplier_areas
join supplier_languages using (supplierid)
join supplier_products using (supplierid)
join supplier using (supplierid)
where
    supplier_areas.locationid=1 
and supplier_languages.languageid in (1,2)
and supplier_products.productid in (....)

As middus already said, take a deep look into the JOIN statement..

Answer 3

Ohh that's great, how would that work in a case of certain tables won't be queried each time? For example products isn't searched or a field in the supplier table requires a where clause