selecting categories and sub categories

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

selecting categories and sub categories

Post by GeXus »

I have a two tables, one categories and one sub_categories, the sub_categories are assocated to a category, so a category may have 10 sub_categories, etc.

I want to display this as such

Category 1
Sub Category 1
Sub Category 2

Category 2
Sub Category 1
Sub Category 2
Sub Category 3

This is the query i have so far...

Code: Select all

SELECT a.name AS cat_name, a.category_id AS cat_id, b.sub_category_id AS sub_id, b.name AS sub_name
FROM `categories` a
LEFT JOIN sub_categories b ON a.category_id = b.category_id
GROUP BY cat_id, sub_id
ORDER BY cat_name ASC
LIMIT 0 , 30
The problem is that the category name repeats for however many sub categories there are.

Does anyone know how I would do it so the category name only repeats once, regardless of the number of sub categories?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your script remembers the last category name displayed. When it changes, display the new one, otherwise keep displaying only the sub-category name.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Is that the best way? Also, i'm not so sure if that will work, since im using smarty templates to display the data... altho there may be a work around.
Post Reply