How do display category name and each products it has

I have three tables in my database

the category_products table has id of both categories and products

How should i use the category_products table to display category name and the product information under each category?.

The product table and category table

3 Answers

This structure is called many-to-many association. The intermediate table category_products connects records from category and product by storing combinations of IDs. Thus a category can have multiple products while a product can also have multiple categories.

To query this structure, use the following SQL:

SELECT categories.cat_name, products.* FROM categories LEFT JOIN category_products ON categories.cat_id = category_products.cat_id LEFT JOIN products ON category_products.prd_id = products.prd_id ORDER BY categories.cat_name; 

You can fetch the data in PHP like so, for example:

$result = mysql_query("SELECT categories.cat_name, products.* FROM categories LEFT JOIN category_products ON categories.cat_id = category_products.cat_id LEFT JOIN products ON category_products.prd_id = products.prd_id ORDER BY categories.cat_name;"); if (!$result) { die('Error: ' . mysql_error()); } $current_category = ''; while ($row = mysql_fetch_assoc($result)) { if($row['cat_name'] != $current_category) { echo $row['cat_name'] . "\n"; echo "--------------------\n"; $current_category = $row['cat_name']; } // output your product fields, you need to adapt these names to your columns echo $row['prd_name'] . " | " . $row['prd_price'] . "\n"; } 

Note the treatment of the cat_name field which holds the category name for each product record. Since you probably don't want to repeat the category name in every row, the name is buffered and only printed when the category changes.

The following query will return name of product with coresponding category.

SELECT prd.name, cat.name FROM products AS prd JOIN category_products AS cp ON prd.id=cp.prd_id JOIN categories AS cat ON cat.id=cp.cat_id

Based off @Wamprirue's answer, this one should get you the specific result you're looking for.

SELECT cat.cat_name, COUNT(cp.prd_id) FROM products AS prd JOIN category_products AS cp ON prd.prd_id = cp.prd_id JOIN categories AS cat ON cat.id = cp.cat_id 

should produce an output with all category names plus a count of how many products match that category.

Without additional information regarding your tables and how they're structured, we can't really give you a solution which will 100% definitively work without a couple edits being necessary on your end.