如何将HTML表单用户输入存入MySQL数据库？PHP新手报错求助

Let's break down why your data isn't saving to the database and fix each issue step by step:

Key Issues Identified #

1. Form submits to the wrong page: Your form in main.php sends data to welcome.php, but your database insertion code lives in index.php. So the INSERT query never runs when the form is submitted.
2. SQL runs before form submission: index.php executes the INSERT every time the page loads (even when no form data exists), leading to empty values and syntax errors.
3. Missing quotes + SQL injection risk: You're directly inserting user input into the SQL string without escaping it, which causes syntax issues and leaves you vulnerable to SQL injection. The kleur value also wasn't wrapped in quotes, breaking the query when empty.
4. Invalid HTML in welcome.php: You're echoing a second <body> tag inside the existing one, which is invalid and can cause rendering issues.

Fixed Code for Each File #

1. main.php (Update Form Action)

Change the form to submit to index.php (where the database logic is) and add a submit button name to check for form submission:

<main>
    <center>
        <form action="index.php" method="post">
            Naam: <input type="text" name="name"><br>
            Achternaam: <input type="text" name="lastname"><br>
            Je favoriete kleur: <input type="color" name="favcolor" value="#ff0000">
            <input type="submit" name="submit_form"> <!-- Add name to detect submission -->
        </form>
    </center>
</main>

2. index.php (Fix Database Logic & Add Session Handling)

We'll only run the INSERT when the form is submitted, use prepared statements to avoid SQL injection, and store user data in a session to pass to welcome.php:

<?php
session_start(); // Start session to share data with welcome.php

// Database settings
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "bezoeker";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Only run database logic if form was submitted
if (isset($_POST['submit_form'])) {
    // Get form data with safe defaults
    $name = trim($_POST['name'] ?? '');
    $lastName = trim($_POST['lastname'] ?? '');
    $favColor = $_POST['favcolor'] ?? '#ff0000';

    // Use prepared statement to prevent SQL injection
    $sql = "INSERT INTO formulier (naam, achternaam, kleur) VALUES (?, ?, ?)";
    
    if ($stmt = $conn->prepare($sql)) {
        // Bind variables (sss = 3 string values)
        $stmt->bind_param("sss", $name, $lastName, $favColor);
        
        // Execute and handle result
        if ($stmt->execute()) {
            // Store user data in session for welcome page
            $_SESSION['user'] = [
                'name' => $name,
                'lastname' => $lastName,
                'favcolor' => $favColor
            ];
            // Redirect to welcome page
            header("Location: welcome.php");
            exit;
        } else {
            echo "ERROR: Could not save data. " . $stmt->error;
        }
        
        $stmt->close();
    } else {
        echo "ERROR: Could not prepare query. " . $conn->error;
    }
}

$conn->close();

// Load templates
include 'template-parts/header.php';
include 'template-parts/main.php';
include 'template-parts/footer.php';
?>

3. welcome.php (Fix HTML & Use Session Data)

Use the session data to display personalized content and set the background color correctly:

<?php session_start(); ?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <link href="css/reset.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <title>Welkom!</title>
    <style>
        /* Set background color in the head */
        body {
            background-color: <?php echo $_SESSION['user']['favcolor']; ?>;
        }
    </style>
</head>
<body>
    <div class="welcome-message">
        <h1>Welcome <?php echo htmlspecialchars($_SESSION['user']['name']); ?> <?php echo htmlspecialchars($_SESSION['user']['lastname']); ?>!</h1>
        <h2>Leuk dat je er bent!</h2>
        <h3>Wouw, mijn favoriete kleur is ook <?php echo $_SESSION['user']['favcolor']; ?>!</h3>
    </div>
    <?php
    // Clear session data to avoid reusing it on refresh
    unset($_SESSION['user']);
    ?>
</body>
</html>

Additional Notes #

• Security: We used htmlspecialchars() when echoing user input to prevent XSS attacks, and prepared statements to block SQL injection. These are critical best practices.
• Default Values: Added fallback values for form fields to avoid undefined variable errors.
• Redirect: After saving data, we redirect to welcome.php to avoid duplicate form submissions if the user refreshes the page.

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