CODEWITHSUNDEEP
phpmysql
user3237819
user3237819

Reputation:

How do display category name and each products it has

Tables in database

I have three tables in my database

category_products table

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?.

enter image description here

The product table and category table

Upvotes: 0

Views: 690

Answers (3)

janwschaefer
janwschaefer

Reputation: 609

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.

Upvotes: 1

esqew
esqew

Reputation: 44699

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.

Upvotes: 0

MacEncrypted
MacEncrypted

Reputation: 314

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

Upvotes: 0

Related Questions