Correctly Displaying Data In Optgroups

I am having an issue with MYSQL displaying results correctly when I add the php code to the snippet below. When it runs it displays everything correctly except when I click on the

Solution 1:

Here is the alternative method, which only takes 1 query. Take a look at how I ordered the query it's basicaly the same idea as with the other answear except you're not making N query * the number of departments

<?php
echo '<select name="test[]" id="optgroup" class="ms" multiple="multiple">', "\n";
$select = mysql_query( '
    SELECT
        *
    FROM `users`, `departments`
    WHERE `users`.`dept` = `departments`.`dept_name`
    ORDER BY `departments`.`dept_name`, `users`.`user_name` ASC
' );
$department = false;
if( mysql_num_rows( $select ) ){
    while( $row = mysql_fetch_array( $select ) ){
        if( ! $department || ( $department && $department != $row['dept_name'] ) ){
            // use some sort of character escapeing to prevent XSS
            if( $department ){
                echo '</optgroup>', "\n";
            }
            echo '<optgroup label="', htmlspecialchars( $row['dept_name'] ), '">', "\n";
            $department = $row['dept_name'];
        }
        echo '<option value="', htmlspecialchars( $row['user_name'] ), '">',
            htmlspecialchars( $row['user_name'] ),
        '</option>', "\n";
    }
    echo '</optgroup>', "\n";
}
echo '</select>', "\n";

Solution 2:

you should first get department names and then for each department, execute a query :

<?php 
echo '<select name="test[]" id="optgroup" class="ms" multiple="multiple">';
$departments = mysql_query( 'SELECT DISTINCT dept_name FROM `departments`' );
while( $row = mysql_fetch_array( $departments ) ){
    // use some sort of character escapeing to prevent XSS
    echo '<optgroup label="', htmlspecialchars( $row['dept_name'] ), '">';
    $users = mysql_query( sprintf( 'SELECT user_name FROM `users` WHERE users.dept = "%s"', mysql_real_escape_string( $row['dept_name'] ) ) );
    while( $user = mysql_fetch_array( $users ) ){
        echo '<option value="', htmlspecialchars( $user['user_name'] ), '">',
            htmlspecialchars( $user['user_name'] ),
        '</option>';
    }
    echo '</optgroup>';
}
echo '</select>';