How to get user ID, username from PHP and MySQL while user is logged in

Question

I have this issue where I can't get user ID and username from database and store it into variable while user is logged in.(Only works with e-mail.) This is my code.

<?php
    if (isset($_POST['email'], $_POST['password'])) {
      $email = $_POST['email'];
      $password = md5($_POST['password']);
      if (empty($email) or empty($password)) {
        $error = 'All fields are requred!';
      }
      else {
        $query=$pdo->prepare('SELECT * FROM user WHERE email = ? AND password = ? ');
        $query->bindValue(1, $email);
        $query->bindValue(2, $password);
        $_SESSION['email']=$email;
        $query->execute();
        $num=$query->rowCount();
        if ($num == 1) {
          $_SESSION['logged_in'] = true;
          header('Location: index.php');
          exit();
        }
        else {
          $error = 'Incorrect data';
        }
      }
    }
  }
?>

Please explain what you mean with "while a user is logged in" - if a user has an active session and navigates to a .php page you simply execute the code? or do you mean at the time of logging in? — JoSSte, Apr 24 '22 at 14:20
I use this code to login into web page and I need to get ID of this user from MySQL and store it into some $_SESSION variable but it only works with $_SESSION['email'] variable. — Richard Gaál, Apr 24 '22 at 14:24
There is another if statement before so it might look like there is too many }. — Richard Gaál, Apr 24 '22 at 14:26
@RichardGaál Please [edit] your question to include the table definition of the `user` table as a `CREATE TABLE` statement. — Progman, Apr 24 '22 at 17:58
@RichardGaál You shouldn't use `SELECT *`, see https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — Progman, Apr 24 '22 at 17:59
@RichardGaál Do not use your own password hashing system, use `password_hash()` and `password_verify()` instead, see https://stackoverflow.com/questions/30279321/how-to-use-phps-password-hash-to-hash-and-verify-passwords — Progman, Apr 24 '22 at 18:00

score 0 · Answer 1 · answered Apr 24 '22 at 15:20

0

Delete bindValue rows and try to execute like this

$query=$pdo->prepare('SELECT * FROM user WHERE email = ? AND password = ? ');
$query->execute([$email, $password]);

Look at examples here https://www.php.net/manual/en/pdo.prepare.php

answered Apr 24 '22 at 15:20

Michal Kumžák

29
2

Sudhir · Accepted Answer · 2022-04-25T12:07:56.990

You are calling the "execute" method of prepared statement but nowhere you are calling the "fetch" method. So in your code when you get rowcount as 1, you are setting a session variable indicating successful login. There you need to add following code:

$row = $query->fetch(PDO::FETCH_ASSOC);

Now the the variable $row will have all your fields and only then you can add values to session variables like user id. So assuming your user table has "user_id" as the field, you can add code like this:

if ($num == 1) {
    $row = $query->fetch(PDO::FETCH_ASSOC);
    $_SESSION['user_id'] = $row['user_id'];
    $_SESSION['logged_in'] = true;
    header('Location: index.php');
    exit();
}

How to get user ID, username from PHP and MySQL while user is logged in

2 Answers2