GROUP BY but show all results

Hey there, I believe this can be done either with SQL or PHP but because I'm more familiar to PHP I decided to post this here.

Ok, here's my problem: I have a database table that stores each product with their subcategories and categories.

Ex:

ID Name Category Subcategory
1 xxx yyy zzz
2 ttt yyy ccc

Ok, I guess there is no problem so far. What I want to do is, to fetch all results but show them under their related category. If I use GROUP BY in the SQL for this that would fetch only the category names therefore I won't be able to fetch all the results. I've thought to fetch first the categories and then create another sql to fetch all the subcategories, but this means a lot of queries which I don't really want to. Here is what I want in example:

Category 1
result 1
result 2
result 3
Category 2
result 4
result 5
result 6
and so on...

Thank you in advance

 

You are going to make many queries. This is where object (oop) is going to shine for you.

First make a product object, then a search/category object. The search/category object queries the db with GROUP BY to get your categories while leaving within the object a resulting products empty, then or simultaneously build your product object and store them in an array back into the search/category object. Once you are done with all that you can then work with the object back at your entry point and output it however you want.

Best
Levi

 

You should be able to do something like:

(PSEUDO CODE)

SELECT name, category, subcategory FROM tablename

foreach result
    if subcategory == '' // is a parent?
        array[category]['parent'] = name
    else // is a child?
        array[subcategory][category] = name

Code (markup):