Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Mysql query question : how to ?

Options
  • 19-01-2009 6:08pm
    #1
    Registered Users Posts: 648 ✭✭✭


    Hi there


    I have a category table with nested categories
    the table is like this

    id, parent_id,title

    i would like to present a dropdown to the user so they can pick the category however i want to show the path to the cateorry in the dropdown
    so as opposed to

    cat 1
    cat 2
    cat 3

    i would have
    cat 1 -> cat 2
    cat 3 -> cat 4

    etc

    i saw this done in a component a few years ago but i cant for the life of me remember where or find similar code


    anyone know how i would do that ?
    THANKS


    ps its the mysql that i need - the rest i can do !)


Comments

  • Registered Users Posts: 762 ✭✭✭Terminator


    You could add another field to your table, e.g., path where you can specify the full path for each category

    e.g., cat 2's path would be

    cat 1 / cat 2

    cat 1's path would be

    cat 1

    if you have a sub category cat 7 within cat 2 its path would be

    cat 1 / cat 2 / cat 7

    and so on

    Then you simply order your dropdown resultset by path asc. I've used this method for directories which had many sub-categories and the categories needed to be ordered correctly for the site submissions page.

    or if your sub categories are only 1 level deep you can do two queries - first select all categories with no children (or parent_id) and then as you run through the recordset do another query to select that parent's children.


  • Registered Users Posts: 648 ✭✭✭ChicoMendez


    hi there

    yes that could be a nifty solution alright

    however do you mean storing the category ids in that extra column - if so then how with mysql statement can we get the categry titles for us in the select ?

    tnx alot


  • Registered Users Posts: 68,317 ✭✭✭✭seamus


    Basically what you're trying to do is represent an n-level tree within a relational database. It's a popular and much-debated question - I can't give you anything solid, as I've yet to use an elegant solution myself; I try to steer clear of them :D

    This might be good:
    http://www.dbmsmag.com/9603d06.html

    It may be a bit heavy on the academics, but that might be a good thing.


  • Registered Users Posts: 762 ✭✭✭Terminator


    As seamus says there is probably an easier way to do it but what I mean is storing the full category name in the path field (see below)

    Or you could just store the same info in your title field and use a function to strip everything up to and including the last / when you need to display the category (title) without its path.

    'Arts And Entertainment'
    'Arts And Entertainment/Architecture'
    'Arts And Entertainment/Artists'
    'Arts And Entertainment/Film'
    'Arts And Entertainment/Galleries'
    'Arts And Entertainment/Literature'
    'Arts And Entertainment/Literature/Authors'
    'Arts And Entertainment/Literature/Authors/George Bernard Shaw'
    'Arts And Entertainment/Literature/Authors/James Joyce'
    'Arts And Entertainment/Literature/Authors/Oscar Wilde'
    'Arts And Entertainment/Literature/Authors/William Butler Yeats'


Advertisement