PHP+MySQL实现仅显示当前登录用户信息的问题求助

Hey there! Let's work through the issues you're facing so logged-in users only see their own information, and fix those error messages along the way.

First, Let's Break Down the Root Causes #

• score.php: Your SQL query has syntax errors (wrong quotes around the column name, invalid variable reference) and isn't properly pulling the logged-in user's username from the session.
• profile.php: You're missing a WHERE clause to filter for the current user, haven't started the session to access logged-in user data, and you're looping through all users instead of just displaying the current one.

Fixed score.php Code #

<?php 
// Start session FIRST to access logged-in user data
session_start();
error_reporting(E_ALL); 
ini_set('display_errors', TRUE); 
ini_set('display_startup_errors', TRUE); 

// Use the connection from db_connect.php instead of re-connecting
require("db_connect.php");

// Add a check to block unauthorized access
if (!isset($_SESSION['Username'])) {
    echo "Please log in to view your scores.";
    exit;
}

// Use prepared statements to avoid SQL injection and syntax errors
$stmt = $con->prepare("SELECT ID, Username, Score, Gamedate, QuizTitle FROM Score WHERE Username = ?");
$stmt->bind_param("s", $_SESSION['Username']);
$stmt->execute();
$result = $stmt->get_result();

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>" . htmlspecialchars($row['ID']) . "</td>";
    echo "<td>" . htmlspecialchars($row['Username']) . "</td>";
    echo "<td>" . htmlspecialchars($row['Score']) . "</td>";
    echo "<td>" . htmlspecialchars($row['Gamedate']) . "</td>";
    echo "<td>" . htmlspecialchars($row['QuizTitle']) . "</td>";
    echo "</tr>";
}
echo "</table>";

// Clean up database resources
$stmt->close();
mysqli_close($con);
?>
<!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">
</head>
<body>
</body>
</html>

Key Fixes for score.php: #

1. Moved session_start() to the very top (required to access $_SESSION variables).
2. Removed the duplicate database connection (reuse the one from db_connect.php).
3. Added a login check to stop unlogged users from accessing score data.
4. Replaced the broken SQL query with a prepared statement to fix syntax errors and block SQL injection attacks.
5. Used htmlspecialchars() when outputting data to prevent cross-site scripting (XSS) vulnerabilities.

Fixed profile.php Code #

<?php 
// Start session FIRST to access logged-in user data
session_start();
include_once 'db_connect.php'; 

// Block unauthorized access
if (!isset($_SESSION['Username'])) {
    echo "Please log in to view your profile.";
    exit;
}

// Use prepared statement for safe, targeted querying
$stmt = $con->prepare("SELECT FirstName, Surname, Email, Username, Date_Creation FROM Users WHERE Username = ?");
$stmt->bind_param("s", $_SESSION['Username']);
$stmt->execute();
$result = $stmt->get_result();
$row = mysqli_fetch_array($result); // No loop needed—we only need one user's data
?>
<!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>
</head>
<body>
<main>
<?php
if ($row) {
    echo "<br />Your <b><i>Profile</i></b> is as follows:<br />";
    echo "<b>First name:</b> ". htmlspecialchars($row['FirstName']);
    echo "<br /><b>Last name:</b> ". htmlspecialchars($row['Surname']);
    echo "<br /><b>Email:</b> ". htmlspecialchars($row['Email']);
    echo "<br /><b>Username:</b> ". htmlspecialchars($row['Username']); // Fixed typo from "Year" to "Username"
    echo "<br /><b>Date created :</b> ". htmlspecialchars($row['Date_Creation']);
} else {
    echo "Profile not found.";
}

// Clean up database resources
$stmt->close();
mysqli_close($con);
?>
</main>
</body>
</html>

Key Fixes for profile.php: #

1. Added session_start() to access the logged-in user's username.
2. Added a login check to block unauthenticated users.
3. Added a WHERE clause to filter the query for the current user's username.
4. Used a prepared statement for security and to avoid syntax issues.
5. Removed the unnecessary loop (we only need to fetch one user's data).
6. Fixed the typo where you had "Year" instead of "Username" in the output.
7. Added htmlspecialchars() to prevent XSS vulnerabilities.

Why Your Original Queries Failed #

• score.php: The query SELECT * FROM Score where 'Username' LIKE _['Username'] had two critical issues:
  • Single quotes 'Username' treat the column name as a string literal instead of a database column (use backticks `Username` if needed, or just the column name directly).
  • _['Username'] is invalid syntax—you meant $_SESSION['Username'] to pull the logged-in user's username from the session.
• profile.php: You didn't add a WHERE clause to filter for the current user, so it returned every user in the Users table.

内容的提问来源于stack exchange，提问作者ThirdYearChild