如何获取用户名和匹配的ID为Session？

Question

I am trying to fetch different stats from whatever user is logged in. Then echo these stats out. currently when logging in i am setting a session with the username. Then i am trying to fetch the id from this username and check it again the table with the same ID and then fetch the rows from that table.

My guess is that since i am starting a session with only the username on login the code to fetch data wont work since the session does not provide the id row. i am unsure of how to get it to do that or if i am even right about that.

I appreciate all help, i am really stuck here.

This is my login code:

$query = "SELECT password FROM users WHERE username = '$username'";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_assoc($result);

    //USERDATA
    $dbPassword = $row['password'];

    if (password_verify($password, $dbPassword))
    {
        // echo "The details are correct.";
        $_SESSION['loggedin'] = $username;
    require_once('../../frontend/templates/account-actions.php');
    }
    else
    {
        echo "Passwords do not match!";
    }

This is my code to fetch the stats data from the id of the username logged in:

$id = $_SESSION['loggedin'];
$query = "SELECT * FROM stats WHERE id='$id'";
$stmt = mysqli_query($conn, $query);
$result = mysqli_fetch_all($stmt,MYSQLI_ASSOC);

Answer 1

I have converted your code to mysqli Prepared Statement with Procedural approach.

$username = "username_to_search";
$password = "password"; //Password is in plain text since password hash has been used.

$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE username = ?");

/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $username); //"s" defines the type of data in the following variables, i.e. String for $username.

/* execute query */
mysqli_stmt_execute($stmt);

mysqli_stmt_store_result($stmt);

$total_rows = mysqli_stmt_num_rows($stmt);

mysqli_stmt_bind_result($stmt, $id_fetched, $username_fetched, $password_fetched); //store every field fetched from the table in sequence. Note that I have added _fetched to make it easier to identify later.

if ($total_rows > 0) {
    while(mysqli_stmt_fetch($stmt)) {
        if (password_verify($password, $password_fetched)) {
            $_SESSION['user_id'] = $id_fetched;
            $_SESSION['username'] = $username_fetched;

            require_once('../../frontend/templates/account-actions.php');
        }
        else {
            echo "Invalid Password!";
        }
    }
} else {
    echo "Invalid Username!";
}

Once you have stored the SESSION variables properly, now you can easily find everything related to this. User your $_SESSION["user_id"] to search.