从mysql回忆下拉菜单需要独特的选择

I am learning PHP and MySQL and in doing so I am trying to do a little database driven website. I am tinkering with database driven dropdown form lists and so far can display them on the data entry form, can capture the result and insert it into database and now I am trying to recall the selected item, display it on the update/edit form. So far with the below code I can recall the data into update/edit form on a dropdown list however I want to narrow the selectable items down to just the unique roles.

My code is:

<?php

include $_SERVER['DOCUMENT_ROOT']."/connections/connectdb.php";

$role_query = "SELECT fer_users.id fer_roles_id, fer_roles.description
"
    . "FROM fer_users
"
    . "JOIN fer_roles
"
    . "WHERE fer_users.fer_roles_id=fer_roles.id";
$role_result = mysqli_query($connection, $role_query);
?>

<select name="update_role">
    <?php 
    while ($role_row = mysqli_fetch_assoc($role_result)) {  
    $role_id=$role_row["fer_roles_id"];
    $role_description=$role_row["description"];
    $selected = ($role_row['fer_roles_id'] == $id);
    echo "<option value=\"$role_id\" ".($selected ? " selected=\"selected\"":"").">$role_description</option>";
    }
    ?>
</select>  

The result of the SQL query is as follows:

id |     role
-----------------
8  | Administrator
16 | Administrator
9  |     User
14 |     User
15 |     User

And my drop down list renders as follows:

As noted above I need the list in the picture to just show Administrator and User.

Just run two queries.

<?php

include $_SERVER['DOCUMENT_ROOT']."/connections/connectdb.php";

$role_query = "SELECT fer_roles.id, fer_roles.description
"
. "FROM fer_roles
"
. "WHERE 1";
$role_result = mysqli_query($connection, $role_query);
$role_query2 = "SELECT fer_users.id, fer_users.fer_roles_id 
"
. "FROM fer_users
"
. "WHERE id = '".mysql_real_escape_string($_GET['id'])."'"; //this assumes you're passing the user as a GET variable
$role_result2 = mysqli_query($connection, $role_query2);
$role_row2 = mysqli_fetch_assoc($role_result2);
?>

<select name="update_role">
<?php 
while ($role_row = mysqli_fetch_assoc($role_result)) {  ?>
    <option value="<?php echo $role_row['id'];?>" <?php if($role_row['id'] == $role_row2['fer_roles_id']){ echo 'selected="selected"';}?>><?php echo $role_row['description'];?></option>";
}
?>
</select>  

May not be the most elegant answer but it should get the job done :)

Edit: the above is if you're trying to update one specific user. If you want a list of all users and their roles this would do it:

<?php

include $_SERVER['DOCUMENT_ROOT']."/connections/connectdb.php";

$role_query = "SELECT fer_roles.id, fer_roles.description
"
. "FROM fer_roles
"
. "WHERE 1";
$role_result = mysqli_query($connection, $role_query);
$role_query2 = "SELECT fer_users.id, fer_users.fer_roles_id 
"
. "FROM fer_users
"
. "WHERE 1"; //this assumes you're looking up a specific user
$role_result2 = mysqli_query($connection, $role_query2);
while($role_row2 = mysqli_fetch_assoc($role_result2)){
?>

    <select name="update_role">
    <?php 
    while ($role_row = mysqli_fetch_assoc($role_result)) {  ?>
        <option value="<?php echo $role_row['id'];?>" <?php if($role_row['id'] == $role_row2['fer_roles_id']){ echo 'selected="selected"';}?>><?php echo $role_row['description'];?></option>";
    }
?>
</select>  
<?php
} ?>

Add GROUP BY fer_roles.description

to the end of your query. This will group your results together by role description.

To include all of the IDs for each role, also change

SELECT fer_users.id fer_roles_id, fer_roles.description

to

SELECT GROUP_CONCAT(fer_users.id fer_roles_id SEPARATOR ','), fer_roles.description

This will cause the id field to return a comma-separated list of each of the IDs.

Documentation on GROUP_CONCAT()

<?php 
    $roles = array();
    while ($role_row = mysqli_fetch_assoc($role_result)) {  
       $roles[$role_row["fer_roles_id"]]=$role_row["description"];
    ?>
<select name="update_role">
    <?php 
    foreach($roles as $role_id => $role){
       $selected = ($role_id == $id);
       echo '<option value="'.$role_id.'" '.($selected ? ' selected ' : '').'>'.$role.'</option>';
    }
    ?>
</select>

Are you wanting the ID returned to be the User ID, or the Role ID?

If the former, then you'd end up with duplicates as presumably there are multiple users with those roles.

If the latter, then wouldn't you want to modify the select to only look at the Roles table?