Page 1 of 1
MySQL - Selecting all child listings within categories
Posted: Thu Jan 28, 2010 7:48 pm
by Mr Tech
I have a MySQL database that includes categories and listings...
The categories table is laid out like this:
id | parent_id | category_name
The listings table is laid out like this:
id | category_id | listing_name
Basically at the moment how it works is when you click on a category, it shows the sub categories and listings within the category.
What I am wanting to do is if you click on a category, I want it to show the listings within that category and also show the listings within all the sub categories and the listings within the sub sub categories etc etc. So basically it will show everything that's under that category?
How is this possible? Can this be done within one SQL statement or would I have to create some sort of looping function that puts the listings into an array?
Thanks for your help!
Re: MySQL - Selecting all child listings within categories
Posted: Fri Jan 29, 2010 6:28 pm
by VladSun
Re: MySQL - Selecting all child listings within categories
Posted: Sun Feb 14, 2010 6:33 pm
by Mr Tech
OK, so I've been having a play and got it working... That however only gets the listings from the sub categories of the current category... This was my code:
Code: Select all
SELECT t1.*, t2.* FROM {$tbl_name}listings_items AS t1
LEFT JOIN {$tbl_name}listings_categories AS c2 ON c2.parent = '" . make_safe($category) . "'
LEFT JOIN {$tbl_name}listings_items AS t2 ON t2.cat = c2.id
WHERE (t1.cat='" . make_safe($category) . "' AND t1.status='published' AND t1.deleted='n') OR (t2.status='published' AND t2.deleted='n')
GROUP BY t2.id
I also want it to select the listings from the sub categories of the sub categories... this is the code I tried but it doesn't seem to work. Any ideas what I am doing wrong?
Code: Select all
SELECT t1.*, t2.* FROM {$tbl_name}listings_items AS t1
LEFT JOIN {$tbl_name}listings_categories AS c2 ON c2.parent = '" . make_safe($category) . "'
LEFT JOIN {$tbl_name}listings_items AS t2 ON t2.cat = c2.id
LEFT JOIN {$tbl_name}listings_categories AS c3 ON c3.parent = c2.id
LEFT JOIN {$tbl_name}listings_items AS t3 ON t3.cat = c3.id
WHERE (t1.cat='" . make_safe($category) . "' AND t1.status='published' AND t1.deleted='n') OR (t2.status='published' AND t2.deleted='n') OR (t3.status='published' AND t3.deleted='n')
GROUP BY t2.id
My code looks really messy and I don't think I'm doing it correctly...
Any help would be greatly appreciated

Re: MySQL - Selecting all child listings within categories
Posted: Thu Feb 18, 2010 8:52 am
by VladSun
Try the "Nested Set Model"

Re: MySQL - Selecting all child listings within categories
Posted: Thu Feb 18, 2010 8:57 am
by Eran
Take a look at this presentation -
http://www.slideshare.net/billkarwin/sq ... trike-back
Tree-structure modeling start at slide 48
Re: MySQL - Selecting all child listings within categories
Posted: Thu Feb 18, 2010 5:58 pm
by Mr Tech
So am I going to have to set-up this lft and rgt thing in my database in order to make this work?
Re: MySQL - Selecting all child listings within categories
Posted: Fri Feb 19, 2010 2:35 am
by VladSun
They will work better

Also, I'd suggest you to add a third column - node depth. So, using the hierarchy from the article, we will have:
Code: Select all
+-------------+----------------------+-----+-----+-------+
| category_id | name | lft | rgt | depth |
+-------------+----------------------+-----+-----+-------+
| 1 | ELECTRONICS | 1 | 20 | 0 |
| 2 | TELEVISIONS | 2 | 9 | 1 |
| 3 | TUBE | 3 | 4 | 2 |
| 4 | LCD | 5 | 6 | 2 |
| 5 | PLASMA | 7 | 8 | 2 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 | 1 |
| 7 | MP3 PLAYERS | 11 | 14 | 2 |
| 8 | FLASH | 12 | 13 | 3 |
| 9 | CD PLAYERS | 15 | 16 | 2 |
| 10 | 2 WAY RADIOS | 17 | 18 | 2 |
+-------------+----------------------+-----+-----+-------+
This way, many of the queries in the article can be simplified.