Mysql query question : how to ?

ChicoMendez

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 !)

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.

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

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

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.

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'