[mr_Boombastic]

Hi there.

I wonder if someone can point me in the right direction here.

I have a database that holds 2 tables with some rows as below:


Category
category_id,
category_name


Product
id,
items,
category_id,
price


I am trying to display each category_name with a list of corresponding items beneath it.

My SQL query is as follows:

$sql=”SELECT * FROM product AS p
    LEFT JOIN category AS c
    ON p.category_id=c.category_id
    GROUP BY c.category_name"; 

Unfortunately this only displays each category_name with one item (row) beneath.

As a complete newcomer to SQL, I am hoping this is due to something obvious.

Not sure if this is relevant but I used phpMyAdmin (on XXAMP) to build the database.

I have tried the query without the alias’s also, but my error report advised me to use them.

Any advice would be sincerely appreciated.

[Jyncka]

How many different category ids do you have in the Category table? If you only have one row, it'll return one row. Group by condenses the result set into rows that represent a group of rows, determined by the column you're grouping by. So if you have one category id in the Category table and you're grouping by that as you are in the query, you will only get one row back.

[mr_Boombastic]

Hi Jyncka.

I have 2 different category_id in the category table.

[Jyncka]

Originally Posted by mr_Boombastic

Hi Jyncka.

I have 2 different category_id in the category table.

Sorry, I misread your post a little and you did say

Originally Posted by mr_Boombastic

Unfortunately this only displays each category_name with one item (row) beneath.

It sounds like group by isn't what you need, a more advanced user can correct me on this, but you could try creating a pivot table which would allow you select rows from the Category table and use them as columns with corresponding rows for each category id.

Here is a guide on doing this: pivot tables

[mr_Boombastic]

Thanks Jyncka.

Its a shame. I was kind of hoping there would be a magic line I could type to make it work !

Having had a cursory glance at pivot tables, it looks like I will need to get reading

[r937]

Originally Posted by mr_Boombastic

I am trying to display each category_name with a list of corresponding items beneath it.

what you are looking for is ORDER BY, not GROUP BY

[mr_Boombastic]

Hi r937.

I need the category to show just once with the products below it.

ORDER BY will list the category name each time a row is returned.

For example:

JEANS
Pepe Jeans
JEANS
Levi

TRAINERS
Nike
TRAINERS
Addidas
TRAINERS
Puma

Unless I can put a DISTINCT somewhere in my sql ?

[Jacques1]

Hi,

database systems are not made for displaying pretty tables. Their sole purpose is to store and provide data. How this data gets formatted is up to the application.

So if you wanna display those categories and products in a certain way, you loop through the rows with PHP (or whatever language you're using). And every time you have a new category, you generate this category heading.

That's it. No "DISTINCT", no "GROUP BY". Just order the rows by category and do the formatting with your scripting language.

Note that the way you're using "GROUP BY" makes no sense and is actually wrong. MySQL lets you do it, but on other database systems, this wouldn't even work. You'd simply get an error message.

The sole purpose of "GROUP BY" is to apply an aggregate function like COUNT(), SUM() etc. to certain groups of rows rather than all rows as a whole. It's not "grouping" in the sense of: I have one category with many elements underneath it. That's not what "GROUP BY" means. As a rule of thumb: If you have a "GROUP BY" in your query but no aggregate function, you're using it wrong. You probably want "ORDER BY" or "DISTINCT".

When using "GROUP BY", you can only select aggregate expressions and the columns listed in the "GROUP BY" clause. You cannot select other columns, because there's simply no definite value for them.

Take your example: The JEANS "group" has two products, Pepe Jeans and Levi's. Now, what is the product your database system is supposed to display for the JEANS "group"? Pepe Jeans? Levi's? Nobody knows.

Like I said, MySQL lets you do it and will simply choose an arbitrary value -- making thousands of MySQL users believe this is how "GROUP BY" works. But that's wrong. It violates the SQL standard* and only works in MySQL (whether you call it a bug or a feature is up to you).



*Actually, the new SQL standard from 2003 will also let you select columns which are functionally dependent on the "GROUP BY" columns, because those do have a definite value. But that's a different topic.

[r937]

Originally Posted by Jacques1

So if you wanna display those categories and products in a certain way, you loop through the rows with PHP (or whatever language you're using). And every time you have a new category, you generate this category heading.

nicely explained

and it's worth repeating that ORDER BY is crucial for this strategy

Originally Posted by Jacques1

*Actually, the new SQL standard from 2003 will also let you select columns which are functionally dependent on the "GROUP BY" columns, because those do have a definite value. But that's a different topic.

and i would call it an advanced topic

nice post, jacques

[mr_Boombastic]

Thanks for your help guys.

I am new to SQL and PHP so apologies for my ignorance.