MySQL - Selecting all child listings within 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

MySQL - Selecting all child listings within categories

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL - Selecting all child listings within categories

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: MySQL - Selecting all child listings within categories

Post 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 :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL - Selecting all child listings within categories

Post by VladSun »

Try the "Nested Set Model" ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL - Selecting all child listings within categories

Post by Eran »

Take a look at this presentation - http://www.slideshare.net/billkarwin/sq ... trike-back
Tree-structure modeling start at slide 48
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: MySQL - Selecting all child listings within categories

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL - Selecting all child listings within categories

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply