Setting option in a select dynamically with PHP

[Adamhumbug]

HI All,

 

I am creating a dropdown box on an edit user form.  The box is populated from a table ssm_role and relates to the user which is another table.

I am wanting the value of the dropdown to be set as whatever the loaded user has in their database table.

See the images below.

$sql = "SELECT * FROM ssm_role ORDER BY role_name ASC";
if($result = mysqli_query($conn, $sql)){
if (mysqli_num_rows($result)>0){
while ($row = mysqli_fetch_array($result)){
echo "<option value='".$row['role_id']."'>".$row['role_name']."</option>";
}
}
}

This is what i am currently doing to make the dropdown but obviously this isnt even attempting to set the default.

Your help is always appreciated

[gw1500se]

So what is your question or what is the error?

[requinix]

1 hour ago, Adamhumbug said:

obviously this isnt even attempting to set the default

Is that the question? How to make a particular one the default?

Add the "selected" attribute to the default <option>. Which you have to determine on your own.

<option value='(role_id)' selected>(role_name)</option>

 

[Psycho]

You obviously need to retrieve the user's previously selected value first. You could do that independently (in a separate query) OR combine it into the query you have to retrieve the options. Note, don't use "SELECT *" = only query the fields you are using. Also, your logic is over-complicated. The first if() statement will always return true even if the query fails since the condition is to check if the results of the query can be assigned to the variable $result. Also, there's no reason to check if the results have more than 1 row before running the while loop. If there are no records, the while loop would not run.

If you pull the selected option independently, you could use something like this

$userSelection = //Some value retrieved from DB

$sql = "SELECT role_id, role_name FROM ssm_role ORDER BY role_name ASC";
$result = mysqli_query($conn, $sql)
if(!$result)
{
    //Add error handling
}
else
{
    while ($row = mysqli_fetch_array($result))
    {
        $selected = ($row['role_id']==$userSelection) ? ' selected="selected"' : '';
        echo "<option value='{$row['role_id']}'{$result = mysqli_query($conn, $sql)}>{$row['role_name']}</option>";
    }
}

Or you could change your query to pull the roles and the users selection at the same time

$sql = "SELECT r.role_id, r.role_name, us.userSelectedRoleId
        FROM ssm_role r
        JOIN table_with_user_selection us
        WHERE us.user_id = $userID
        ORDER BY r.role_name ASC";

Then, using the same loop above, change the $selected code to this

$selected = ($row['role_id']==$row['userSelectedRoleId']) ? ' selected="selected"' : '';

 

[Barand]

I find it easier to get options from a function, then the current value (in this case the "$user_role") can be passed as a parameter

function roleOptions($conn, $current) {
    $sql = "SELECT role_id
                 , role_name
            FROM ssm_role 
            ORDER BY role_name ASC";
    $opts = '';        
    if($result = mysqli_query($conn, $sql)) {
        if (mysqli_num_rows($result)>0){
            while ($row = mysqli_fetch_array($result)){
                $sel = $row['role_id'] ==  $current ? 'selected' : '';
                $opts .= "<option $sel value='".$row['role_id']."'>".$row['role_name']."</option>";
            }
        }
    }
    return $opts;
}

HTML

<select name="role">
    <?= roleOptions($conn, $user_role) ?>
</select>