MySQL displays every user's information instead of the logged in one

Hello everyone! I’m new in back-end and I’m currently working on a project where I’ve created a user registration and sign in form with sessions. I’m building a profile page where I will display the logged in user information but my PHP code displays every user’s information on a page, I tried to add session to data fetching but I’m always getting a lot of erros, could someone please help me out on this? It’d be greatly appreciated! Thanks in advance, Here is my code:

At the very top:

<?php

session_start();

if (!isset($_SESSION['loggedin'])) {
	header('Location: login.php');
	exit;
}
?>

Before the section where I want to display the data:

<?php
       include_once 'php/connection.php';
       
        $sql = "SELECT id, firstname, lastname FROM users";
        $result = $conn->query($sql);
        // output data of each row
        while($row = $result->fetch_assoc()) {   
    ?>
<h1><?php echo $row["firstname"]; ?></h1>

After the section where I want to display data:

<?php
            
}

$conn->close();
            
?>

You’ve asked MySQL for all records in the users table. If you only want the entry for a single user then you need to use a WHERE clause in the query

2 Likes

Welcome to the Sitepoint forums.

This query is asking for id, firstname, lastname from the users table but has not specified which row you want. In such a query you could add something like WHERE id=97 - this would find you the user with id 97. But I don’t think that is how you want this to work.

Your login routine has probably already assigned these values to session variables eg _SESSION['id'], _SESSION[‘firstname’], $_SESSION[‘lastname’]. Have a look through the login script and see if you can see this. If so then you can then just use the variables to say, for instance

echo "Hello, {$_SESSION['firstname']} {$_SESSION['lastname']} - welcome to my website.";

Btw, don’t put this site on a live Internet site until you have learned about secure SQL queries using prepared statements.

Get back to us if you can’t figure things out.

2 Likes

Thank you guys for the answers and yes, the problem was that I didn’t specify which entry I needed but after adding

WHERE email='".$_SESSION['name']."'

to the SQL statement it worked as I wanted it to work.
Here is the full statement:

$sql = "SELECT firstname, lastname FROM users WHERE email='".$_SESSION['name']."'";

Although I believe that this will be SQL injection vulnerable?

Yes. You need to use a prepared statement rather than putting $_SESSION['name'] in your query string.

I’m very new in PHP and SQL so I tried to do this:

        $sess = $_SESSION['name'];
    
        $stmt = $conn->prepare("SELECT firstname, lastname, email, phone, birthday, gender FROM users WHERE email=$sess)
        values(?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("ssssss", $firstname, $lastname, $email, $phone, $birthday, $gender);
        $stmt->execute();
        $result = $conn->query($sql);

But it’s giving me this error:

Fatal error : Uncaught Error: Call to a member function bind_param() on bool in C:\xampp\htdocs\profile.php:80 Stack trace: #0 {main} thrown in C:\xampp\htdocs\profile.php on line 80

I think you need:

$sql="SELECT firstname, lastname, email, phone, birthday, gender FROM users WHERE email=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $email);
$stmt->execute();

where $email is the variable that is the email. Although you seem to be asking for the email when you already know the email. I may have misunderstood what you are trying to do here. Presumably the user has logged by this stage. You can obtain all this info when they log on and store it in the

$_SESSION
variable (eg
$_SESSION['lastname'], $_SESSION['email']

In the login handler script I have defined session name to be the email of a user:

session_regenerate_id();
$_SESSION['loggedin'] = TRUE;
$_SESSION['name'] = $_POST['email'];
$_SESSION['id'] = $id;

And my main goal is to create a profile page where I will display all the information of a user which is already logged in.

In that case you need:

sess = _SESSION[‘name’];

$sql="SELECT firstname, lastname, email, phone, birthday, gender FROM users WHERE email?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $sess);
$stmt->execute();

Because $sess is the email. But I suggest that the naming you are using is confusing and may cause problems when/if you or anyone visits the code in the future - in the loginhandler script I would use

$_SESSION['email'] = $_POST['email'];

But you could obtain this information at the same time as the user logs in. Presumably the login involves a SELECT query to check that eg email and password (using password_hash) exist and match? You could get the info from the other columns(firstname, lastname etc.) then and store them in the session variable, along with the email.

Well, thanks for the code, I did change naming in login handler to this:

$_SESSION['email'] = $_POST['email'];

Sorry I know I’m being a headache but I tried to run this code:

$sess = $_SESSION['email'];
    
$sql="SELECT firstname, lastname, email, phone, birthday, gender FROM users WHERE email = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $sess);
$stmt->execute();
    
$result = $conn->query($sql) or die($conn->error);

Although it’s showing me another error now :frowning_face::

Commands out of sync; you can’t run this command now

You are not being a headache! I have had some great help here from brains much bigger than mine - it is great to be able to help out others. And I enjoy a challenge.

When dealing with reults from statemented queries you need to code a little differently. I think you need:

$sess = $_SESSION['email'];

$sql="SELECT firstname, lastname, email, phone, birthday, gender FROM users WHERE email?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $sess);
$stmt->execute();

if ($stmt->error){echo "something has gone wrong";}
$result=$stmt->get_result();                                 
$row = mysqli_fetch_array ( $result, MYSQLI_ASSOC ) ;

If you post your login scripts I may be able to show you how you can get this info at the same time that the user logs in.

Oh man thank you so much for this effort, everything is working as intended and planned :pray: I really appreciate it and hope that I’ll be able to help others as well soon! Sure I can drop the login handler script here.

<?php
session_start();

$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = 'register';

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if ( mysqli_connect_errno() ) {
	
	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}

if (!isset($_POST['email'], $_POST['password']) ) {
	
	header('location: ../login.php?incorrect');
}


if ($stmt = $con->prepare('SELECT id, password, activation_code FROM users WHERE email = ?')) {
	
	$stmt->bind_param('s', $_POST['email']);
	$stmt->execute();
	
	$stmt->store_result();
    
    if ($stmt->num_rows > 0) {
	$stmt->bind_result($id, $password, $activation_code);
	$stmt->fetch();
	
        
        
        
	if (password_verify($_POST['password'], $password)) {
		
        
        if ($activation_code == 'activated') {
            session_regenerate_id();
            $_SESSION['loggedin'] = TRUE;
            $_SESSION['email'] = $_POST['email'];
            $_SESSION['id'] = $id;
            header('Location: ../index.php');
            
        } else {
            header('location: ../login.php?notactivated');
            exit();
        }
        
	} else {
		
		header('location: ../login.php?incorrect');
	}
} else {
	
	header('Location: ../login.php?incorrect');
}


	$stmt->close();
}
?>

I think this should work:

<?php
session_start();

$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = 'register';

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if ( mysqli_connect_errno() ) {
	
	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}

if (!isset($_POST['email'], $_POST['password']) ) {
	
	header('location: ../login.php?incorrect');
}

if ($stmt = $con->prepare('SELECT id, password, firstname, lastname, email, phone, birthday, gender, activation_code FROM users WHERE email = ?')) {
	
	$stmt->bind_param('s', $_POST['email']);
	$stmt->execute();
	
	$stmt->store_result();
    
    if ($stmt->num_rows > 0) {
	$stmt->bind_result($id, $password, $firstname, $lastname, $email, $phone, $birthday, $gender, $activation_code);
	$stmt->fetch();
	
        
        
        
	if (password_verify($_POST['password'], $password)) {
		
        
        if ($activation_code == 'activated') {
            session_regenerate_id();
            $_SESSION['loggedin'] = TRUE;
            $_SESSION['email'] = $_POST['email'];
            $_SESSION['id'] = $id;
            $_SESSION['firstname'] = $firstname;
            $_SESSION['lastname'] = $lastname;
            $_SESSION['phone'] = $phone;
            $_SESSION['birthday'] = $birthday;
            $_SESSION['gender'] = $gender;
            
                        
            header('Location: ../index.php');
            
        } else {
            header('location: ../login.php?notactivated');
            exit();
        }
        
	} else {
		
		header('location: ../login.php?incorrect');
	}
} else {
	
	header('Location: ../login.php?incorrect');
}


	$stmt->close();
}
?>

You can probably see the three places I have altered, so that you are requesting more fields in the SELECT statement and then assigning those values to the session variable. You can then retrieve them whenever you like, during the session, on other pages, eg

echo "Hello, {$_SESSION['firstname']} {$_SESSION['lastname']} - welcome to my website.";

without having to make further queries.
Let me know if this works or throws any errors.

1 Like

Yeap, it works perfectly fine, thanks again for your help and dedication today, I’m happy I found this community :heart:

1 Like

I think, also, for security purposes, it is advisable to have your database connection script in a separate php file, in a different folder, with an include to it in any page that needs to use it.

In the login code, the only value you should store in a session variable is the user’s id. You would then use this value to query on each page request to get any other user data, such as the user’s name, permissions, … The reason for doing this is so that any changes made to the user’s data, either by the user or by a moderator/administrator, will take effect on the very next page request. When you store these values in session variable(s) when the user logs in, they won’t change until the user logs in again.

This redirect indicates that your form and form processing code are not on the same page. This requires that you write a bunch of extra logic for error messaging and it provides a bad user experience since you cannot re-populate the form fields with their existing values upon an error, requiring the visitor to keep re-entering all the data values over and over.

The only redirect you should have in your form processing code is upon successful completing, with no errors, to the exact same url of the current page, to cause a get request for that page. This will prevent the browser from trying to re-submit the form data if you refresh or browse away from and back to that page.

You should also have an exit/die statement, consistently, after every header() redirect to stop php code execution. A header() statement only tells the browser something. It has no affect on php code execution.

1 Like

Thank you - good to know.

I’m always learning.