php - Mark checkbox as checked if user has a role

one text

Solution:

Role query is just checking if user has any role so it will return always true (if user has at least 1 role) or always false (if user has no role).

Instead

$new_sql = "SELECT role_id from users_roles, users WHERE users_roles.user_id = '".$row["user_id"]."' GROUP BY users_roles.user_id";

You have to add a filter by role_id.

$new_sql = "SELECT role_id from users_roles, users WHERE users_roles.user_id = '".$row["user_id"]."' AND users_roles.role_id = '" . $a . "' GROUP BY users_roles.user_id";

Note: As sugested in the comments you should use parameterized queries to protect against sql injection attacks. Also you should escape variables before echoing to protect against cross-site scripting (xss) attacks.

Above change should fix your problem but I have some suggestions. Check below.

Suggestion 1: You are assuming that role_id is the same as $count index. If you don't have a sequential id in roles table that starts with 1 or by any chance the result order is changed then code will break.

$roles array needs to track role ID. Subsequent code need to change accordingly.

Assuming column id in roles table:

$records_roles = mysqli_query($connect, "SELECT id, role_name FROM roles");
$roles = array();
$count = 0;

while ($course_roles = mysqli_fetch_assoc($records_roles)){
    // Keep track of roles ID
    $roles[] = [
        'id' => $course_roles['id'],
        'name' => $course_roles['role_name'],
    ];
    
    $count++;
}

Suggestion 2: You are executing a database query to check for each role individually. This affects code performance.

Get all roles of the user in 1 query and then compare role id in the array.

Complete code with 2 suggestions will look like this

<?php
// Display user information on table
$query = "SELECT user_id, first_name, last_name, email FROM users ORDER BY user_id ASC";
$result = mysqli_query($connect, $query);

$records_roles = mysqli_query($connect, "SELECT id, role_name FROM roles");
$roles = array();

while ($course_roles = mysqli_fetch_assoc($records_roles)){
    // Keep track of roles ID
    $roles[] = [
        'id' => $course_roles['id'],
        'name' => $course_roles['role_name'],
    ];
}

while($row = mysqli_fetch_array($result))
{
    echo "
    <tr>
    <td style='display:none'>".$row['user_id']."</td>
    <td>".$row['first_name']."</td>
    <td>".$row['last_name']."</td>
    <td>".$row['email']."</td>
    <td>
    <form method='post'>
    Select user roles<br/>
    <input type='hidden' name='user_id' value=".$row["user_id"].">
    ";

    // Get all user roles
    $new_sql = "SELECT role_id FROM users_roles WHERE user_id = '".$row["user_id"]."' GROUP BY user_id";
    $user_roles_result = mysqli_query($connect, $new_sql);
    $user_roles = [];
    while ($row_user_roles = mysqli_fetch_assoc($user_roles_result)){
        // Put user roles in array
        $user_roles[] = $row_user_roles['role_id'];
    }
    
    foreach ($roles as $role) {
        if (in_array($role['id'], $user_roles)) {
            echo "<input type='checkbox' checked name='techno[]' value='{$role['id']}' />" .$role['name']. "<br>";
        } else {
            echo "<input type='checkbox' name='techno[]' value='{$role['id']}' />" .$role['name']. "<br>";
        }
    }

    echo "
    <button class='btn btn-primary' type='submit' name='checkSubmit' >Submit</button>
    </form>
    </td>
    ";
    
    echo "
    </tr>
    ";
}