Skip to content
PHP Logo

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.

php
// 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
}