Skip navigation

ColdFusion

Shaffer Calculating status...

Currently Being Moderated

Parent Child Data - Populate within a Dropdown

Apr 27, 2012 9:02 AM

Tags: #dropdown #child #parent #recursive

Hi All ...

I have a mysql database with the following fields

ID (Int) - Title (VarChar) - ParentID (Int)

This table is used to display categories with it's parent. Categories with the ParentID of 0 means that it is a root category.

What'm trying to do is query the database and populate all values (with it's parent & child(s)) within a regular html dropdown box.

So the output of the dropdown whould look like:

Home

Home > Sub Cat 1

Home > Sub Cat 1 > Sub Sub Cat 1

Home > Sub Cat 1 > Sub Sub Cat 2

Home > Sub Cat 1 > Sub Sub Cat 3

Home > Sub Cat 2 > Sub Sub Cat 1

Home > Sub Cat 2 > Sub Sub Cat 2

Home > Sub Cat 3

Home > Sub Cat 4

Home > Sub Cat 4 > Sub Sub Cat 1

Home > Sub Cat 5

Any ideas on how I can accomplish this?

Many Thanks

Shafiq

601 Views 3 Replies Latest reply: jbreslow, May 2, 2012 9:59 AM

Replies

Currently Being Moderated

1. Adam Cameron.,

Apr 27, 2012 9:16 AM in reply to Shaffer

Report

As far as I know you cannot elegantly handle this sort of datastructure in MySQL (this is based on 5min of googling). If you had Oracle you could use CONNECT BY PRIOR, and with SQL Server one can use Common Table Expressions to effect much the same thing (except in a pretty clunky fashion, IMO).

As far as I can tell, MySQL has neither of those options. You'll need to do some sort of horrific recursive proc, or something like that. Although I saw references to MySQL not even being able to do that (I didn't check how contemporary those comments were).

If you can possibly ditch that way of modelling your data - it really doesn't scale so well - have a look at nested set hierarchies:
http://en.wikipedia.org/wiki/Nested_set_model

The only modification I make to that is to store the parent with each node too, which makes sibling operations much easier. Nested sets work really well with hierarchical look-ups though.

--
Adam

|
Mark as:
Currently Being Moderated

2. BKBK,

Apr 28, 2012 2:01 AM in reply to Shaffer

Report

You ask 2 implicit questions, one about the data and one about the display. My suggestion is on display.

To display the parent and child nodes, you might want to think about using a cftree. This link contains a fully worked out example (at the end) which you can run directly.

|
Mark as:
Currently Being Moderated

3. jbreslow,

May 2, 2012 9:59 AM in reply to Shaffer

Report

Take a look at Ben Nadel's article on Recusion, it was a huge help for me when i was doing something similar. After getting his code to work in your environment, I think all you would have to do is tweak the output and populate a pulldown...

http://www.bennadel.com/blog/1069-Ask-Ben-Simple-Recursion-Example.htm

Make sure to check out the comments, there is example code in there on how to pass the previous level's value to the next itiration.

|
Mark as:

Forum guidelines

Answers + Points = Status

10 points awarded for Correct Answers
5 points awarded for Helpful Answers
10,000+ points
1,001-10,000 points
501-1,000 points
5-500 points