SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Selecting sub categories
-
Jan 17, 2005, 15:46 #1
Selecting sub categories
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?
-
Jan 17, 2005, 16:44 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 17, 2005, 18:10 #3
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
-
Jan 17, 2005, 18:21 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by romance
-
Jan 17, 2005, 18:30 #5
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?
-
Jan 17, 2005, 18:45 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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 --Code: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
-
Jan 17, 2005, 18:55 #7
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
-
Jan 17, 2005, 19:00 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
WHERE L1.parent_catid = 4 (or 5) instead of WHERE L1.parent_catid is null
-
Jan 17, 2005, 19:09 #9
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
-
Jan 18, 2005, 13:27 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no idea
worked for me
mysql 4.0.16
Bookmarks