Populating HTML Select Dropdown With Optgroups Based on Database Categories

[eluser]Vheissu[/eluser]
I just can't wrap my head around this problem. I'm trying to populate a dropdown select with my categories, only thing is that a category can have a parent and for every main category I would like it to output an optgroup and then the associated child category names into my list.

My table structure is as follows (categories table):

category_id (auto incremented value)
category_name
category_description
category_parent (if this is a main category the value is 0 and if the category has a parent, this is the id of the parent category)
category_status (a value of 1 means it is enabled)

How would I go about pulling out all categories with a category_parent of 0 and then that categories associated values? keep in mind the main categories will be an optgroup label and child categories as values inside of the optgroup.

The category will need to have a status of 1 as well, otherwise it shouldn't be outputted.

I've got no code to post as all attempts I've made would probably only confuse you. Thank you in advance.

[eluser]Dennis Rasmussen[/eluser]
How deep can a child (category) go?
You can read more about hierarchical data here: http://dev.mysql.com/tech-resources/arti...-data.html

Otherwise you can use a recursive method with a PHP array/function.

[eluser]Vheissu[/eluser]
[quote author="Dennis Rasmussen" date="1285616945"]How deep can a child (category) go?
You can read more about hierarchical data here: http://dev.mysql.com/tech-resources/arti...-data.html

Otherwise you can use a recursive method with a PHP array/function.[/quote]

Hi Dennis,

A category only goes one deep. There is always only going to be a few main categories, but no sub main categories.

This is for a band classifieds site I am building and the main categories are; Bands Seeking Musicians, Musicians Seeking Bands and Music Equipment for sale.

Both the musicians seeking and bands seeking will have children categories, but never any other main categories. So the categories would be; Bass Players, Lead Guitarists, Rhythm Guitarists, Vocalists..

I know I should be using nested foreach's, but I'm not sure if I can get away with using one MySQL query or I have to use two and the combine the results. Any examples you can provide me? I appreciate the help.

The example link you provided explains what I want to do perfectly, but I would prefer using as much PHP as possible to produce the results. I don't want to slow the site down with complicated SQL queries that could be done less intensively in PHP.

[eluser]crikey[/eluser]
You can do this with one query and a foreach loop to build your structure so that you can iterate over it in your view and generate your drop down box.

Here's the logic that I'd follow - which might not be the most efficient way btw. I'll leave you to implement it into code

I'd iterate over the query results (presumably an array of table rows where each row is an array) and build an array ($new) with the following format:

$new[category_id] = array('parent' => category_data, 'children' => array of category_data)

$new is an array with each key being the category_id for a parent; category_data is an array that represents a record from your table.

As you iterate the results you only want to add to the $new array if the category_status == 1. For each result: if it's a parent, add it to $new[category_id]['parent']; if it's a child, add it to $new[category_parent]['children'].

So you could reference $new[1234]['parent'] to get data for the parent (whose id is 1234) and $new[1234]['children'] to get the array of the child categories. When you iterate to build your dropdown box, you want to iterate over $new. For each element, check if ['children'] is empty and, if it is, output the ['parent'] as an option, otherwise output the ['parent'] as an optgroup and iterate over ['children'], outputting them as options.

Clear as mud?

Cheers,
Grant

[eluser]Vheissu[/eluser]
Nice one Crikey. Very understandable, I'll have a go at implementing it tonight once I finish work. Thanks for providing a well explained answer. I guess it's not the code I struggle with, just coming up with the way to do things in my head is the part I struggle with sometimes.