PHP/MySQL Nested <select> using <optgroup> with LEFT JOIN on same table in a single query.

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.

4 thoughts on “PHP/MySQL Nested <select> using <optgroup> with LEFT JOIN on same table in a single query.”

Leave a Reply

Your email address will not be published. Required fields are marked *