Sessions and understanding SQL / requires

[Pandee]

Hello!

So i have an echo like such

<?php echo $_SESSION['email']; ?>

Is there documentation or perhaps provide an example where/how I can pull this email from the same row of information in the MySQL database? Doing this without a require_once php page that has this information?

 

Googling didn't really help.

 

Many thanks for being part of my journey!

Edited January 28, 2021 by Pandee

[Barand]

To get the email from a database you would use a SQL SELECT statement.

https://dev.mysql.com/doc/refman/5.7/en/select.html

[Pandee]

        $sql = "SELECT username, verified, email FROM users WHERE username, verified, email";
         
        if($stmt = mysqli_prepare($link, $sql)){
            // prepare statements as parameters
            mysqli_stmt_bind_param($stmt, "sis", $param_username, $param_verified, $param_email);
            
            //  parameters
            $param_username = $username;
            $param_email = $email;
            $param_verified = $verified;

            if(mysqli_stmt_execute($stmt)){
                // 
                $_SESSION['username'] = $user['param_username'];
                $_SESSION['verified'] = $user['param_verified'];
                $_SESSION['email'] = $user['param_email'];

            } else{
                //empty
            }

            // empty
            
        }

Above is code I have been trying to get to work. I believe something is wrong with my select statement that you recommended me to use (also thanks). I can tell because when I try to do the below code...

 

<?php echo $_SESSION['username']; ?>

<?php echo $_SESSION['verified']; ?>

<?php echo $_SESSION['email']; ?>

Username is echo'd and email and verified does not.

 

Thanks once again!

[Barand]

Your WHERE clause is suspect. Where username, verified and email are what? And if you know what they are, why the query?

What is you query supposed to do?

Where is $user defiined. You seem to be missing the steps that bind the result and fetch the data returned by the query.

[Pandee]

Hmm...

I did it because I wanted to know the username and email of the user who is logged in alongside a tinyint if they are verified or not.

My query just wants to take that information from the database then send it to the echos

[Barand]

So the only condition you are interested in is "WHERE email = ?"

[Pandee]

Apologies, correct me if I am not on the same page. But are you talking about what sort of information I want from this query? If so, just Email and Username and Verified.

[Barand]

What information do you know about the logged in user?

(I was assuming you have the email already in the session array, but perhaps it's the username or the id (recommended) ).

[Pandee]

So I have set up a different page which has inserted their Username / Password / Token / Email and verified set to 0 when registration is successful. 

When I enter the welcome.php file it would echo the Username and Email to let them know who they are logged in as and what address the verification email was sent to.

[Barand]

When a user logs in, what do you store in $_SESSION so that

• you know they are logged in, and
• they can be identified?

(ideally, this would be the id of their user record)

[Pandee]

According to my DB "id" is my primary key. and "id" "username" "loggedin" are stored in session variables.

[Barand]

Then

$stmt = mysqli_prepare($link, "SELECT username
                                    , verified
                                    , email 
                               FROM users 
                               WHERE id = ?
                               ");
mysqli_stmt_bind_param($stmt, "i", $_SESSION['id']);         
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $username, $verified, $email);
mysqli_stmt_fetch($stmt);

echo "$username, $email, $verified";

However, I'd recommend using PDO instead of mysqli

$stmt = $pdolink->prepare("SELECT username
                                , verified
                                , email 
                           FROM users 
                           WHERE id = ?
                           ");
$stmt->execute([$_SESSION['id']]);
$row = $stmt->fetch();
echo "{$row['username']}, {$row['email']}, {$row['verified']}"