How to get user other data when they login in with thier username?

I’m a newbie in programming. I want to ask is there any way I can get user’s data from MySQL in another page when they login in only with their username and password. What I’m facing is

1. I have a login page and main page
2. I have a user database that include user id, username, password and role_id

When the user login, I can get their username thru $SESSION, but what I really want is their role_id so I can use it as a condition to limit the user. Like Admin can get a header bar that has more button and function.

But when I tried to use sql query, it only shows table instead a single text that I can use. Any idea?

main page.php

<?php
// Initialize the session
session_start();

$user = $_SESSION['username'];
$conn = new mysqli('localhost','root','','mes');
$sql = "SELECT role_id from users where username = $user";
$result = $conn->query($sql);
echo $result;

// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}

// condition to limit the user
if( (isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] == true) && ($result === '202000') ){
    include_once "premierHeader.php";
}
    else{
      include_once "header.php";
    }

?>

Answer

WARNING as some people have already pointed out in the comment section, you’re wide open to SQL injections (SQLI Attacks). I highly suggest you make use of parametized, prepared statements using either mysqli or PDO.

With that out of the way, I will be using mysqli prepared statements for my answer specifically.

What you need to understand is that when you get the result of your query, you will be given an object. To actually get a value, you will need to fetch(); it.

Example:

<?php
// Initialize the session
session_start();

// Username variable via session
$user = $_SESSION['username'];

// DB variables
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mes";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if($conn->connect_error){
  die("Connection failed: " . $conn->connect_error);
}

// Declare the query
$sql = "SELECT role_id from users where username = ?";

// Prepare and bind
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $user);

// Execute the query
$stmt->execute();

// Bind result variable and fetch value
$stmt->bind_result($role_id);
$stmt->fetch();

// Close connection
$stmt->close();
$conn->close();

// Optionally, you can put the role id into a session variable
$_SESSION["role_id"] = $role_id;
?>