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

Question

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.

Answer 1

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
} ?>