Newbie need help displaying products and category

aawan

Hi everyone, I'm a PHP newbie, and am having trouble. If anyone can help, I would greatly appreciate it.

I have a query of products and categories. What I would like to do is display the list of all the products by category, with the category displaying only once with all of the corresponding products below it.

Example:

Category A
Product 1
Product 2
.
.
.
Category B
Product 3
Product 4
Product 5

Here is my query:
SELECT p.ProductID, p.ProductCode, p.ProductDescription, c.categoryName
FROM Product p
INNER JOIN productCategory c ON p.productCategoryID = c.productCategoryID

The problem is that the category name displays every time for every product.
I only want it to appear once, above the products associated with it.

Hope this makes sense.
Happy new year to all.

cheers,
aa

Roger_Ramjet

Query results do not display themselves - you have written the code that displays them. It is fundamental to the way relational databases work that you do get all columns for each row - how you then process those columns for display is a seperate matter, SQL can not do it for you.

You can make it easier for yourself by ordering the query results by category name. You then need to track the category as you display the product details and display it once when it changes. Pretty simple logic to this one so see how you get on by yourself first.

aawan

Roger,

Thank you so much for kindly replying to my post.
Yes, bread doesn't toast itself either.
I understand that Query results do not display themselves, which is why I am posting this question to a "PHP" forum, as I understand many kinds of loops in PHP, along with the appropriate "output" functions display them.

Using a simple while loop I was able to display my query results, with the category name displaying each time for each product.

I was hoping someone could help me with the logic as to how I could display the category name once in a separate row, then all the products within that category, then the next category name in a row by itself, then all the products within that category.

Happy holidays and cheers,
AA

Roger_Ramjet


$sql = "SELECT p.ProductID AS prd, p.ProductCode AS cde, p.ProductDescription AS dsc, c.categoryName AS cnm
FROM Product p
INNER JOIN productCategory c ON p.productCategoryID = c.productCategoryID";

$result = mysql_query($sql);

$cat = '';

while ($row = mysql_fetch_array($result)) {
   $cnm = $row['cnm'];
   if ($cat != $cnm) {
       echo $cnm . '<br />';
       $cat = $cnm;
   }
   echo $row['prd'] . ' ' $row['dsc'] . '<br />';
}

aawan

I just wanted to thank you Roger.
That hit the nail right on the head. Exactly what I was looking for.

cheers,
Ali

DESIGNGRAPHY

aawan, you see $row its displaying category products text from your database.