How can the SQL query for user authentication be modified to include fetching user permissions in PHP?

To include fetching user permissions in PHP when authenticating a user with an SQL query, you can modify the query to retrieve the user's permissions along with their other information. This can be achieved by joining the user table with a permissions table based on the user's ID. Once the user is authenticated, you can store their permissions in a session variable for easy access throughout the user's session. 

// Assuming $username and $password are the user input for authentication
$query = "SELECT users.id, users.username, permissions.permission_level 
          FROM users 
          INNER JOIN permissions ON users.id = permissions.user_id 
          WHERE users.username = '$username' AND users.password = '$password'";

$result = mysqli_query($connection, $query);

if (mysqli_num_rows($result) == 1) {
    $row = mysqli_fetch_assoc($result);
    $_SESSION['user_id'] = $row['id'];
    $_SESSION['username'] = $row['username'];
    $_SESSION['permission_level'] = $row['permission_level'];
    // Other authentication logic
} else {
    // Authentication failed
}

Keywords

SQL query user authentication user permissions PHP fetching

Related Questions