I looked around on the web for a while trying to find an example of what I was trying to do. Some were close, but none performed the JOIN portion of the query and on the same table from the SELECT portion (self referencing). So, here is the code I used to created a sub-category only dropdown using the parent categories as the optgroup labels.
$result = mysql_query(" SELECT categories.category_id, categories.category_name, parent.category_name AS parent_label FROM categories LEFT JOIN categories AS parent ON parent.category_id = categories.category_parent_id WHERE categories.category_parent_id != '0' ORDER BY parent_label ASC, categories.category_name ASC "); $current_parent = ''; $dropdown = '<select name="category_id">'; while ($category = mysql_fetch_object($result)) { if ($current_parent != $category->parent_label) { $dropdown .= ($current_parent != '') ? '</optgroup>' : ''; $dropdown .= "<optgroup label=\"{$category->parent_label}\">"; $current_parent = $category->parent_label; } $dropdown .= "\n<option value=\"{$category->category_id}\">{$category->category_name}</option>"; } $dropdown .= "</select>";
I’m sure if you’re reading this post, it will help save you a lot of time and trouble. If nothing else, at least you’ll leave understanding how to do it in the future.
I use this code but it’s not working.
Most likely due to table/column name differences (which you should be able to debug) 😉
optgroup got created for each and every child category. Maybe missing something.
Possibly no parent/child assignments. Hard to say without seeing your table structure, modified query and output.