Display only the current user's details using PHP and MySQL

Question

In this part of the code, both the score table, and the profile page display everything I want, however it is displaying EVERYBODIES details like

First Name Surname Email Category Username However, I want it so that when the user is logged in they can only see THEIR OWN details

This is score.php

    <?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
require("db_connect.php");
session_start();
?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">
    <link rel="icon" href="../../../../favicon.ico">

<?php
$con=mysqli_connect("localhost","username","Password","Database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


$result = mysqli_query($con,"SELECT * FROM Score where 'Username' LIKE _['Username']");


echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Score</th>
<th>Gamedate</th>
<th>QuizTitle</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['Username'] . "</td>";
echo "<td>" . $row['Score'] . "</td>";
echo "<td>" . $row['Gamedate'] . "</td>";
echo "<td>" . $row['QuizTitle'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

this is profile.php

<?php
include_once 'db_connect.php';
?>


<!DOCTYPE HTML>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">
    <link rel="icon" href="../../../../favicon.ico">

    <title>Profile page </title>

 <?php
$con=mysqli_connect("localhost","Username","password","database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT `FirstName`,`Surname`,`Email`,`Username`,`Date_Creation`FROM Users ");



while($row = mysqli_fetch_array($result))
{



        echo "<br />Your <b><i>Profile</i></b> is as follows:<br />";
        echo "<b>First name:</b> ". $row['FirstName'];
        echo "<br /><b>Last name:</b> ".$row['Surname'];
        echo "<br /><b>Email:</b> ".$row['Email'];
        echo "<br /><b>Year:</b> ".$row['Username'];
        echo "<br /><b>Date created :</b> ".$row['Date_Creation'];
}


mysqli_close($con);
?>
    </main>



</html>

These are the errors I recieve when I try to run the page. Before I changed the select query so it picked the current user that is logged in, but it displayed everyone's information

You need a login mechanism. With Sessions you can remember on the server side which user is currently logged in. Then you can load the data only from this user. Very basic example here: https://stackoverflow.com/a/10097986/3623232 — Guillermo, May 18 '18 at 16:59
I already have a login page, where the user logs in and a home page? — ThirdYearChild, May 18 '18 at 17:15
You need to save the username in the session when logging in. E.g. with `$_SESSION['user'] = $username_from_form`. Do not forget the `session_start()` at the beginning of the script. Then you can check the `$_SESSION['user']` variable in your `profile.php` to get the currently logged in username. — Guillermo, May 18 '18 at 17:23
You're open to SQL injection - this should be addressed to ensure a safe site :) — treyBake, May 18 '18 at 17:47
@ThisGuyHasTwoThumbs hmmm I see it. I'm experimenting this for a project. I'll address this when I crack the code — ThirdYearChild, May 18 '18 at 18:43
$query = "SELECT * FROM Users WHERE `Username` = 'Username'"; $result=mysqli_query($con,$query) or die("MySQL error: " . mysqli_error($con) . "
\nQuery: $query");; while($row = mysqli_fetch_assoc($query)) { This is the change i've made, the error i'm now getting Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, string given in line 91 — ThirdYearChild, May 18 '18 at 19:52

score 0 · Answer 1 · answered May 18 '18 at 18:24

0

Single quotes in SQL are used to refer to a specific string like 'Bob'. If you're trying to check the value of the column, don't put the column name in quotes as 'Username'. Instead, put the column name without quotes: where Username like

answered May 18 '18 at 18:24

krubo

5,969
4
37
46

I did this change and it showed Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/students/aatio001/public_html/FYP/Website/Quiz/profile.php on line 92 this is on the while($row = mysqli_fetch_array($result)) line – ThirdYearChild May 18 '18 at 18:41
$query = "SELECT * FROM Users WHERE `Username` = 'Username'"; $result=mysqli_query($con,$query) or die("MySQL error: " . mysqli_error($con) . "
\nQuery: $query");; while($row = mysqli_fetch_assoc($query)) { This is the change i've made, the error i'm now getting Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, string given in line 91 – ThirdYearChild May 18 '18 at 19:52

score 0 · Answer 2 · answered May 18 '18 at 18:54

About the errors. You can use the error handling to show what's the problem on your query. Second, you can use the ID to get the data information of the logged in user. You can save the ID of the user via session, when user logged in saved the ID then use it to your where clause.

score -1 · Answer 3 · answered May 18 '18 at 17:04

-1

in your score.php file, on this line:

$result = mysqli_query($con,"SELECT * FROM Score where 'Username' LIKE _['Username']");

Look at your WHERE statement, seems to be wrong.

And in your profile.php, on this line:

$result = mysqli_query($con,"SELECT `FirstName`,`Surname`,`Email`,`Username`,`Date_Creation`FROM Users ");

You dont pass any WHERE statement. So, the problems are in your SQL, specifically on your wheres.

answered May 18 '18 at 17:04

lpFranz

406
5
22

The intention was to use the same select and where query as the one for score in profile. I'm trying to figure out where because it seems ok – ThirdYearChild May 18 '18 at 17:22
$query = "SELECT * FROM Users WHERE `Username` = 'Username'"; $result=mysqli_query($con,$query) or die("MySQL error: " . mysqli_error($con) . "
\nQuery: $query");; while($row = mysqli_fetch_assoc($query)) { This is the change i've made, the error i'm now getting Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, string given in line 91 – ThirdYearChild May 18 '18 at 19:52
Do you have any login system? Your WHERE, in your above comment, your where condition will bring to you only the user which has the username "Username", is that right? – lpFranz May 18 '18 at 20:02
@IpFranz I do have a login system.. I want my WHERE condition to be that it looks at who is currently logged in and print their information? – ThirdYearChild May 18 '18 at 20:10
Ok, so you have to pass to your where, the credentials (email,pass) that comes from user form, paying attention on SQL injection issues. – lpFranz May 18 '18 at 20:15
@IpFranz, the main goal for me is to get it all to work. I've tried copying everything from Login, but it's not working :-( – ThirdYearChild May 18 '18 at 21:02

Display only the current user's details using PHP and MySQL

3 Answers3