Associate Table Help!

[wrigley]

Ok, please try to help as much as you can, thank you very much!

 

I have 3 tables (kb_category, kb_products, kb_category_products) the kb_category of course holds all the categories including a category_id.  kb_products of course holds all the products including a product_id and category_id. kb_category_products which simple has category_id and product_id.

 

What I need is to have only the products that are in that category be displayed.  So the kb_category_products is to handle exactly that.  I can get the page to realize that kb_category.category_id=3 (or whatever url variable), but how do I get it to read all the category_id=3 from kb_category_products and to output all the product_id's that are listed in kb_category_products.  And then to get those product_id's from kb_products to output all the correct product information.

 

Please let me know if this doesn't make since, I know it's alot and I probaly didn't explain it very well..lol.

 

Again please help me the best you can.

 

Thank You.

[celsoendo]

Do you want the php code to do this or sql queries?

[wrigley]

Well, kinda both I guess....Im new to php and mysql and just trying to find some help.  I'm willing to do whatever I can do to help, I just don't know that much.  I just need the products to display on the page for that specific category.

 

Thanks for your help.

[chronister]

Do the products have multiple categories? If not, then you may be better off dropping the joining table and just store the category_id as part of the product itself.... but I digress...

 

I am guessing at the field names, and it could probably be done a little simpler with a JOIN in there... but this is the way I would do it (if I understand you correctly).

 

I formatted it goofy, to make it a little more readable.

 

SELECT 
  kb_products.product_name, 
  kb_products.product_description, 
  kb_products.product_price, 
  kb_category.category_name 
FROM 
  kb_category, 
  kb_products, 
  kb_category_products 
WHERE 
  kb_category_products.product_id = kb_products.product_id 
&& 
  kb_category_id = '$id'

 

This is obviously not a copy and paste code block.... you will need to modify it for your particular needs, but I think it should give you a good idea how to extract only the information you want.

 

Nate