SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by romance
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    WHERE L1.parent_catid = 4 (or 5) instead of WHERE L1.parent_catid is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no idea

    worked for me

    mysql 4.0.16
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •