Selecting sub categories

[romance]

Hi all,

i'm working on a message board and am having the following issue. I have a threads table with a reference to the category the thread is in (catid)

I then have a list of categories in the following format

name | catid | parent_id

one | 1 | 0
two | 2 0
three| 3 | 1
four | 4 | 1
five | 5 | 2
six | 6 | 4

So that each category can be linked to a parent category. When displaying the categories I basically want to show all threads that are contained in that category and any threads that are in subsequent sub categories. So for the example above clicking on category one would show threads belowing to category one,three,four and six. Is there a way to do this in SQL, or is it the database structure which needs revising?

[r937]

database structure is fine

how many levels down did you want to retrieve in a single query?

your example shows 2 levels down

see http://www.sitepoint.com/forums/showthread.php?t=173153

[romance]

thanks for the link, 5 levels should be fine, but with the example given on the link..is this relating to my threads table or the categories table?

Thought i'd post the full tables for both:

CREATE TABLE subcats (
catid int(11) NOT NULL auto_increment,
parent_catid int(11) NOT NULL default '0',
catname varchar(255) NOT NULL default '',
PRIMARY KEY (catid)
) TYPE=MyISAM;

CREATE TABLE threads (
threadid bigint(20) NOT NULL auto_increment,
thread_title varchar(255) NOT NULL default '',
thread_date datetime NOT NULL default '0000-00-00 00:00:00',
userid bigint(20) NOT NULL default '0',
subcatid int(11) NOT NULL default '0',
PRIMARY KEY (threadid)
) TYPE=MyISAM;

catid parent_catid catname
9 1 Fitness
2 9 Weight loss
3 9 excercise
4 2 burgers
5 4 fast food
6 4 Snacking
7 5 chips and burgers
8 5 kebabs

threadid thread_title thread_date userid subcatid
1 My first thread 0000-00-00 00:00:00 3 4
2 my love for chips and burgers 0000-00-00 00:00:00 3 7

[r937]

... but with the example given on the link..is this relating to my threads table or the categories table?

i'm sorry, i don't understand this question

[romance]

Sorry, time of night here

Looking at the link you posted it seems that something similar to this is required

select L1.catname as L1name
, L2.catname as L2name
, L3.catname as L3name
from subcats L1
left outer
join subcats L2
on L1.catid = L2.parent_catid
left outer
join subcats L3
on L2.catid = L3.parent_catid
where L1.parent_catid is null

And i'm guessing this has to be done on my subcats table, but how is this related to getting the appropriate threads (and any threads in the categories underneath the current subcat) from the threads table?

[r937]

do you have threads at each of those three levels, and do you want to select all threads at each of those levels?

if so, something like this --

select L1.name as name1 
     , L2.name as name2 
     , L3.name as name3 
     , threads.title as threadtitle 
  from subcats as L1 
inner 
  join subcats as L2 
    on L1.id = L2.parent_catid 
inner 
  join subcats as L3 
    on L2.id = L3.parent_catid 
inner 
  join threads 
    on L3.id = threads.subcatid 
 where L1.parent_catid is null 
union all 
select L1.name as name1 
     , L2.name as name2 
     , null 
     , threads.title as threadtitle 
  from subcats as L1 
inner 
  join subcats as L2 
    on L1.id = L2.parent_catid 
inner 
  join threads 
    on L2.id = threads.subcatid 
 where L1.parent_catid is null 
union all 
select L1.name as name1 
     , null 
     , null 
     , threads.title as threadtitle 
  from subcats as L1 
inner 
  join threads 
    on L1.id = threads.subcatid 
 where L1.parent_catid is null 
order 
    by 1 
     , 2 
     , 3 
     , 4

[romance]

wow, thanks every so much for this rudy, much appreciated! One question is, whereabouts would I stick a where clause in to set the current catid (via php)?

e.g for the following:

catid parent_catid catname
9 1 Fitness
2 9 Weight loss
3 9 excercise
4 2 burgers
5 4 fast food
6 4 Snacking
7 5 chips and burgers
8 5 kebabs

i may be passed a querystring ?catid=4 which would show all threads in the burgers,fast food,snacking,chips and burgers and kebabs sub categories and as such show both threads below

threadid thread_title thread_date userid subcatid
1 My first thread 0000-00-00 00:00:00 3 4
2 my love for chips and burgers 0000-00-00 00:00:00 3 7

but being passed catid=5 would only show

threadid thread_title thread_date userid subcatid
2 my love for chips and burgers 0000-00-00 00:00:00 3 7

[r937]

WHERE L1.parent_catid = 4 (or 5) instead of WHERE L1.parent_catid is null

[romance]

getting the following error:

SELECT L1.catname
as name1, L2.catname
as name2, L3.catname
as name3, threads.thread_title
as threadtitle
FROM subcats
as L1
inner join subcats
as L2
on L1.catid = L2.parent_catid
inner join subcats
as L3
on L2.catid = L3.parent_catid
inner join threads
on L3.catid = threads.subcatid
WHERE L1.parent_catid = 4
union all SELECT L1.catname
as name1, L2.catname
as name2,
null , threads.thread_title
as threadtitle
FROM subcats
as L1
inner join subcats
as L2
on L1.catid = L2.parent_catid
inner join threads
on L2.catid = threads.subcatid
WHERE L1.parent_catid = 4
union all SELECT L1.catname
as name1,
null ,
null , threads.thread_title
as threadtitle
FROM subcats
as L1
inner join threads
on L1.id = threads.subcatid
WHERE L1.parent_catid = 4
ORDER BY 1, 2, 3, 4
LIMIT 0, 30

MySQL said:


Column 'name3' cannot be null

[r937]

no idea

worked for me

mysql 4.0.16