Outputting a category followed by the products in it

Question

I have 3 table in a mysql database,category,product and cat_pro,category table contains categories,products table contain products and their prices,cat_pro contain the primary keys of categories and products respectively.A category can contain numerous products. Category table has category_id and category name,products table has product_id,product_name and price.cat_pro table is used during inner joins since it contains the foreign keys of categories and products.

What i want is an sql query statement that outputs a category followed by the products in that category together with their prices until all the categories and products are exhausted in a vertical manner. The problem is outputting the information in the manner below.That is a category followed by its products For example

Category A
product 1 250$
product 2 450$
Category B
product 1 100$
product 2 400$
Category C
product 1 300$
product 2 100$

You can't do that exactly in MySQL, because a row can't be a "member" of another row. — David
It would be clearer to have the mysql schema of your 3 tables. You can click "edit" to add information to your initial question. — Jocelyn

Jimmy Jimmy · Accepted Answer · 2012-07-28T17:20:02

I see you tagged youe question as PHP, so i will give you the PHP-MySQL way to do it. The code should be something like this (adjust column names to your own schema)

<?php
$query = "SELECT * from category c inner join cat_pro cp on c.category_id = cp.category_id
  inner join product p on cp.product_id = p.product_id order by c.cateogry_id desc";
$res = mysqli_query($query);
$lastCat = "";
while($row = mysqli_fetch_array($res)) {
  if($lastCat != $row["category_name"]) echo $row["category_name"]."<br/>";
  echo $row["product_id"]." ".$row["product_price"]."&#36;<br/>";
}
?>

Outputting a category followed by the products in it

1 Answers