使用PHP和PDO向MySQL数据库插入数据遇到问题求助

Hey Judith, let's get your PHP PDO setup working for inserting data into your gildeuitleen database. First, let's break down the gaps in your current Database class and fix them step by step.

1. Fix the Database Connection Class #

Your current class has a constructor that dies immediately, and no way to actually get a working PDO connection. Here's a complete, production-ready version using the singleton pattern (which keeps a single connection instance alive instead of creating new ones repeatedly):

<?php
class Database {
    private static $dbName = 'gildeuitleen';
    private static $dbHost = 'localhost';
    private static $dbUsername = 'root';
    private static $dbUserPassword = 'root';
    private static $cont = null;

    // Prevent direct instantiation of the class
    private function __construct() {}

    // Prevent cloning the connection instance
    private function __clone() {}

    public static function getInstance() {
        if (self::$cont === null) {
            try {
                // Create PDO connection with critical settings for debugging and security
                self::$cont = new PDO(
                    "mysql:host=" . self::$dbHost . ";dbname=" . self::$dbName . ";charset=utf8mb4",
                    self::$dbUsername,
                    self::$dbUserPassword,
                    [
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions for errors
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Return associative arrays
                        PDO::ATTR_EMULATE_PREPARES => false // Disable emulated prepares for better security
                    ]
                );
            } catch(PDOException $e) {
                // Throw the error so you can see exactly what's wrong
                throw new Exception($e->getMessage());
            }
        }
        return self::$cont;
    }
}

Key Fixes & Improvements: #

• Removed the die() in the constructor and made it private to enforce the singleton pattern
• Added a getInstance() method to create and return the PDO connection
• Enabled PDO::ERRMODE_EXCEPTION to get detailed error messages (critical for debugging your insertion issues)
• Set the charset to utf8mb4 to support all Unicode characters (like emojis or special symbols)
• Disabled emulated prepares to strengthen protection against SQL injection

2. Example: Insert Data Safely with the Fixed Class #

Now let's use this class to insert data properly with prepared statements (never concatenate user input directly into SQL queries—it's a huge security risk):

<?php
require_once 'Database.php';

try {
    // Get the PDO connection instance
    $db = Database::getInstance();

    // Prepare your insert statement (use placeholders for dynamic data)
    $stmt = $db->prepare("INSERT INTO your_table_name (column1, column2, column3) VALUES (:val1, :val2, :val3)");

    // Bind your data to the placeholders (you can also pass an array directly to execute())
    $stmt->bindParam(':val1', $yourValue1);
    $stmt->bindParam(':val2', $yourValue2);
    $stmt->bindParam(':val3', $yourValue3);

    // Set your values (this could come from form input, API data, etc.)
    $yourValue1 = "Sample item name";
    $yourValue2 = "Available";
    $yourValue3 = 10;

    // Execute the statement
    if ($stmt->execute()) {
        echo "Data inserted successfully! New row ID: " . $db->lastInsertId();
    } else {
        echo "Failed to insert data.";
    }
} catch(Exception $e) {
    // Catch and display errors (remove this in production and log instead!)
    echo "Error: " . $e->getMessage();
}

3. Common Debugging Tips for Persistent Issues #

If you're still stuck, try these quick checks:

• Double-check that your table name and column names match exactly what's in your MySQL database (case-sensitive on some systems like Linux)
• Verify that your root user has INSERT permissions for the gildeuitleen database
• Enable full error reporting at the top of your PHP files to catch hidden warnings:

  error_reporting(E_ALL);
  ini_set('display_errors', 1);
  

• Confirm your MySQL server is running (localhost is reachable, port 3306 is open)

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