Query after a query

[bradbice]

I have a simple database I'm pulling products and product categories out of for my website. Here's my db set-up:

table: products
prod_id
manu_id
type_id

table: manufacturers
manu_id
manufacturer

table: prodtype
type_id
type

Now, I need my products.php page to display my products but sorted by categories. So, first there would be a list of the types for people to choose from. Simple enough:

SELECT type_id, type FROM prodtype ORDER BY type ASC

and output with php, with the type_id as a variable on the end of the URL. No problem there.

On the next page, I need to show only the manufacturers that are listed in the products table under the previously selected type. I can get the manufacturers name with

$type = $_GET['type'];
SELECT prod_id, manufacturer, type FROM manufacturers m, prodtype pt, products p WHERE (p.type_id = '$type') GROUP BY manufacturer

...and from that I can get a list of the manufacturers, but to move on to my next step, which is listing all the products from the products table that match the previously selected type and manufacturer, I need to pass along the manu_id.

How do I get the manufacturer name and id, while still comparing it to the products table to see which manufacturers are listed under a specific type?

Any help is greatly appreciated!!

[Sxooter]

Try using a join:

SELECT p.type_id, p.type, m.prod_id, m.manufacturer FROM prodtype p join
manufacturers m on (p.type_id = m.type_id)
join prodtype pt on (pt.??? = m.???)
join products pr on (m.??? = pr.???)
ORDER BY p.type ASC

Note that I'm not sure what you should be joining all those tables on. But without a proper join clause, you have an unconstrained join, which means the database has to produce the cartesian product of all those tables (i.e. Table1 X Table2 X Table3, etc...) which is a huge task, and will make your application run quite slow.

[bradbice]

Thank you very much for your help. I will try this suggestion out and see if i can get it to work.