Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Mysql query question : how to ?

  • 19-01-2009 06:08PM
    #1
    Registered Users, Registered Users 2 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, Registered Users 2 Posts: 778 ✭✭✭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, Registered Users 2 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, Registered Users 2 Posts: 68,173 ✭✭✭✭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, Registered Users 2 Posts: 778 ✭✭✭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