基于PHP与phpMyAdmin的登录系统：如何实现已登录用户专属偏好数据的存储

Hey there! Let's work through your questions step by step to get this favorite color feature running smoothly.

1. Fixing the Database Table Relationship #

Your current colors table setup isn't ideal for linking color data to specific user accounts. Here are two solid options to fix this:

Option 1: Add a Column Directly to the users Table (Simplest for Single Color per User) #

If each user only needs to save one favorite color, skip the separate colors table entirely. Just add a new column to your existing users table:

ALTER TABLE users ADD COLUMN favorite_color VARCHAR(50) DEFAULT NULL;

This keeps all user-related data in one place, which is perfect for your test use case.

Option 2: Properly Link the colors Table to users (For Multiple Colors Later) #

If you might want to let users save multiple favorite colors down the line, adjust your colors table structure:

• Keep id as an auto-increment primary key (this identifies individual color entries)
• Add a user_id column (INT NOT NULL) that acts as a foreign key linking to users.id
• Keep favorite_color as your color storage field

Run this SQL to update the table:

ALTER TABLE colors 
  DROP PRIMARY KEY,
  ADD COLUMN user_id INT NOT NULL,
  ADD PRIMARY KEY (id),
  ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

• The ON DELETE CASCADE ensures that if a user is deleted from users, their color entries are automatically removed too (prevents orphaned data).
• If you still want only one color per user, add a unique constraint to user_id:

  ALTER TABLE colors ADD UNIQUE KEY unique_user_color (user_id);
  

2. Yes, You Must Add SESSION Validation to the Form Page #

100% necessary! You need to block unauthenticated users from accessing or submitting the form. Place this code at the very top of your form page (before any HTML output):

<?php
session_start(); // Don't forget this—required to access $_SESSION variables

if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
?>

This ensures only logged-in users can reach the form, so you never have to worry about color submissions that aren't tied to a valid user.

3. Handling Form Submission (Save the Color) #

Here's a complete example using MySQLi (adjust for your database credentials) to process the form and save the color to the database:

Full Form Page Code #

<?php
session_start();

// Login check
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}

// Database connection (replace with your own credentials)
$conn = mysqli_connect("localhost", "your_username", "your_password", "your_database");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Process form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Clean up user input and get user ID from session
    $favorite_color = trim($_POST["favorite-color"]);
    $user_id = $_SESSION["id"];

    // Option 1: Using the modified `users` table (single color per user)
    $sql = "UPDATE users SET favorite_color = ? WHERE id = ?";
    
    // Option 2: Using the linked `colors` table (with unique user constraint)
    // $sql = "INSERT INTO colors (user_id, favorite_color) VALUES (?, ?) ON DUPLICATE KEY UPDATE favorite_color = ?";

    // Prepare statement to prevent SQL injection
    $stmt = mysqli_prepare($conn, $sql);
    
    // Bind parameters for Option 1
    mysqli_stmt_bind_param($stmt, "si", $favorite_color, $user_id);
    
    // Bind parameters for Option 2
    // mysqli_stmt_bind_param($stmt, "iss", $user_id, $favorite_color, $favorite_color);

    // Execute and show feedback
    if (mysqli_stmt_execute($stmt)) {
        echo "<p style='color: green;'>Your favorite color has been saved!</p>";
    } else {
        echo "<p style='color: red;'>Error: " . mysqli_error($conn) . "</p>";
    }

    // Clean up resources
    mysqli_stmt_close($stmt);
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Save Favorite Color</title>
</head>
<body>
    <h1>Save Your Favorite Color</h1>
    <form action="" method="post">
        <label>My favorite color:</label>
        <input type="text" name="favorite-color" value="<?php 
            // Optional: Pre-fill with existing color
            $result = mysqli_query($conn, "SELECT favorite_color FROM users WHERE id = " . $_SESSION["id"]);
            $user = mysqli_fetch_assoc($result);
            echo htmlspecialchars($user["favorite_color"] ?? "");
        ?>">
        <input type="submit" value="Save">
    </form>
</body>
</html>

Key Notes: #

• Always use prepared statements (like above) to avoid SQL injection attacks—never insert user input directly into SQL queries.
• The optional pre-fill code retrieves the user's existing favorite color and populates the input field, which makes the experience more user-friendly.

Final Quick Tips #

• Test the full flow: Log in, submit a color, then check the database to confirm it's linked to your user ID.
• If you're using PDO instead of MySQLi, the logic is identical—just adjust the connection and prepared statement syntax.

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