PHP drop-down list with main item as optgroup and child item as option

Question

I try to create a drop-down list from a database that contains the main items and their child items. I want the main items to become optgroup labels and the child items the option items.

<select>
  <optgroup label="$rs_pa['Name']">
    <option value="$rs_ch['Link']">$rs_ch['Name']</option>
    <option value="$rs_ch['Link']">$rs_ch['Name']</option>
  </optgroup>
  <optgroup label="$rs_pa['Name']">
    <option value="$rs_ch['Link']">$rs_ch['Name']</option>
    <option value="$rs_ch['Link']">$rs_ch['Name']</option>
  </optgroup>
</select>

The above is just a scheme to show where each variable should fit..

$rs_pa['Name] - is the name of the parent item.

$rs_ch['Name] - is the name of the child item.

$rs_ch['Link'] - is the link of the child item.

This is the code I have right now:

$mysql_table_items      = "sys_menu_top";
$mysql_table_options    = "sys_options";

// SQL QUERY
$mysql_result_number    = mysql_query("SELECT VALUE FROM $mysql_table_options WHERE Name LIKE 'nav_menu_elements_on_line_usr'") or die($myQuery . "<br/><br/>" . mysql_error());
$mysql_select_parent    = "SELECT * FROM $mysql_table_items WHERE `Parent` = '0' AND Type LIKE 'top' AND Active = 1 ORDER BY `Order` ASC";
$mysql_select_child     = "SELECT * FROM $mysql_table_items WHERE `Parent` = '" . $rs_pa['ID'] . "' AND Active = 1 ORDER BY `Order` ASC";

// SQL RESULT
$mysql_result_number    = mysql_fetch_array($mysql_result_number, MYSQL_ASSOC) or die("Error: Query Failed! " . mysql_error());
$mysql_result_parent    = mysql_query($mysql_select_parent) or die("Error: Query Failed! " . mysql_error());
$mysql_result_child     = mysql_query($mysql_select_child) or die("Error: Query Failed! " . mysql_error());

$output .= "<select>";
while ($rs_pa = mysql_fetch_array($mysql_result_parent)) {
    $output .= "<optgroup label='" . $rs_pa['Name'] . "'>";
    while ($rs_ch = mysql_fetch_array($mysql_result_child)) {
        $output .= "<option value='" . $rs_ch['Link'] . "'>" . $rs_ch['Name'] . "</option>";
        }
    $output .= "</optgroup>";
    }

$output .= "</select>";

echo $output;

The prblem is that this doesn't show right.. I tried everything but I am not so good with php.. Thank you for any help..

Answer 1

You are referencing to $rs_pa['ID'] on this line:

$mysql_select_child     = "SELECT * FROM $mysql_table_items WHERE `Parent` = '" . $rs_pa['ID'] . "' AND Active = 1 ORDER BY `Order` ASC";

Are you sure $rs_pa['ID'] is defined at that point?

Something like this should work better:

$mysql_table_items      = "sys_menu_top";
$mysql_table_options    = "sys_options";

// SQL QUERY
$mysql_result_number    = mysql_query("SELECT VALUE FROM $mysql_table_options WHERE Name LIKE 'nav_menu_elements_on_line_usr'") or die($myQuery . "<br/><br/>" . mysql_error());
$mysql_select_parent    = "SELECT * FROM $mysql_table_items WHERE `Parent` = '0' AND Type LIKE 'top' AND Active = 1 ORDER BY `Order` ASC";

// SQL RESULT
$mysql_result_number    = mysql_fetch_array($mysql_result_number, MYSQL_ASSOC) or die("Error: Query Failed! " . mysql_error());
$mysql_result_parent    = mysql_query($mysql_select_parent) or die("Error: Query Failed! " . mysql_error());

$output = "<select>";

while ($rs_pa = mysql_fetch_array($mysql_result_parent))
{
    $output .= "<optgroup label='" . $rs_pa['Name'] . "'>";

    $mysql_select_child     = "SELECT * FROM $mysql_table_items WHERE `Parent` = '" . $rs_pa['ID'] . "' AND Active = 1 ORDER BY `Order` ASC";
    $mysql_result_child     = mysql_query($mysql_select_child) or die("Error: Query Failed! " . mysql_error());

    while ($rs_ch = mysql_fetch_array($mysql_result_child))
    {
        $output .= "<option value='" . $rs_ch['Link'] . "'>" . $rs_ch['Name'] . "</option>";
    }

    $output .= "</optgroup>";
}

$output .= "</select>";

echo $output;

Some further improvements could be made:

• Stop using mysql_query and switch to PDO (http://php.net/manual/en/ref.pdo-mysql.php) which is more secure. The mysql_query functions are deprecated starting from PHP 5.5.0 anyway.
• Rewrite your query to fetch all the data you need in a single query. This will really speed up things.
• Separate your PHP from your HTML by using templates.