PHP Session实现用户关联数据库信息查询技术求助

Got it, let's get this sorted for you. The problem right now is your SQL query is fetching all records from the confirm table—there's no filter to narrow it down to just the logged-in user's entries. Here's how to fix that safely:

Step 1: Confirm the Database Link #

First, make sure your confirm table has a column that links each entry to a user (like username or user_id). Since you're using $_SESSION['username'], I'll assume there's a username column in the confirm table that stores the associated user's name.

Step 2: Update the Query with a Filter & Prevent SQL Injection #

Never directly insert user-controlled data (like session values) into your SQL string—it's a huge security risk (SQL injection). Instead, use prepared statements to safely pass the username to the query.

Here's your revised code:

<?php if (isset($_SESSION['username'])) : ?>
    <p>Username: <strong><?php echo $_SESSION['username']; ?></strong></p>
<?php endif ?>

<?php
// Only run the query if the user is logged in
if (isset($_SESSION['username'])) {
    // Prepare the query with a placeholder for the username
    $query = "SELECT food FROM `confirm` WHERE username = ?;";
    $stmt = mysqli_prepare($db, $query);
    
    // Bind the session username to the placeholder
    mysqli_stmt_bind_param($stmt, "s", $_SESSION['username']);
    
    // Execute the query
    mysqli_stmt_execute($stmt);
    
    // Get the result set
    $result = mysqli_stmt_get_result($stmt);
    
    // Check if there are entries to display
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            echo $row['food'] . "<br>";
        }
    } else {
        echo "No entries found for your account.";
    }
    
    // Clean up the statement
    mysqli_stmt_close($stmt);
} else {
    echo "Please log in to view your entries.";
}
?>

Key Changes Explained: #

• Added a check to only run the query if the user is logged in (avoids errors if the session isn't set)
• Used a prepared statement with a ? placeholder instead of directly inserting $_SESSION['username'] into the query (blocks SQL injection attacks)
• Added a WHERE username = ? clause to filter results to only the logged-in user's entries
• Added fallback messages for empty entries or unauthenticated users (improves user experience)

If your confirm table uses a user_id instead of username (a better practice for scalability!), just adjust the query to WHERE user_id = ? and make sure your session stores the user's ID (or fetch it from a users table first using the username).

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