创建PHP数据库连接类时遭遇错误，寻求技术解决方案

Hey there! Let's work through troubleshooting the errors you're hitting with your database connection class. First, let's look at the gaps in your current code and the most common pitfalls that might be causing issues.

首先分析现有代码的潜在问题 #

• Your connect() method is marked as protected, which means you can't call it directly from outside the class—you'll need a public method to access the connection.
• The connection error handling is incomplete; without capturing specific error messages, it's nearly impossible to know why the connection is failing.
• You haven't set a database charset, which can lead to weird encoding issues down the line.
• We need to make sure your config constants are properly defined and accessible.

Step-by-Step Troubleshooting & Fixes #

1. Verify Your Config Constants

First, double-check your includes/config.php file to ensure the required constants are correctly defined (no typos, correct values):

// Example config.php content
define('SERVER_NAME', 'localhost'); // Or your database host (e.g., 127.0.0.1)
define('USERNAME', 'your_database_username');
define('PASSWORD', 'your_database_password');
define('DB_NAME', 'your_target_database');
// Add port if needed: define('DB_PORT', 3307);

Also, confirm the path to config.php is correct—use an absolute path if you're unsure:

include __DIR__ . "/includes/config.php"; // __DIR__ gets the current script's directory

2. Fix Connection Error Handling

Update the connect() method to use mysqli's exception reporting, so you get clear error messages instead of vague failures:

protected function connect(){
    if (!isset(self::$connection)){
        // Enable strict error reporting for mysqli
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        try {
            // Add port parameter if your DB uses a non-default port
            self::$connection = new mysqli(SERVER_NAME, USERNAME, PASSWORD, DB_NAME);
            // Set UTF-8mb4 charset to support emojis and special characters
            self::$connection->set_charset('utf8mb4');
        } catch (mysqli_sql_exception $e) {
            // For development: print the error directly
            die('Database connection failed: ' . $e->getMessage());
            // For production: log the error instead of showing it to users
            // error_log('DB Connection Error: ' . $e->getMessage());
            // header('HTTP/1.1 500 Internal Server Error');
            // exit;
        }
    }
    return self::$connection;
}

3. Add Public Methods to Use the Connection

Since connect() is protected, add public static methods to access the connection or run queries:

// Get the active database connection
public static function getConnection(){
    $instance = new self();
    return $instance->connect();
}

// Run a basic query (with error handling)
public static function query($sql){
    $connection = self::getConnection();
    try {
        $result = $connection->query($sql);
        return $result;
    } catch (mysqli_sql_exception $e) {
        error_log('Query Failed: ' . $e->getMessage() . ' | SQL: ' . $sql);
        die('Query error occurred. Please contact support.');
    }
}

// Optional: Prepped statement method (prevents SQL injection)
public static function prepare($sql){
    $connection = self::getConnection();
    try {
        $stmt = $connection->prepare($sql);
        return $stmt;
    } catch (mysqli_sql_exception $e) {
        error_log('Prepare Failed: ' . $e->getMessage() . ' | SQL: ' . $sql);
        die('Failed to create prepared statement.');
    }
}

4. Test the Connection

Create a simple test script to verify everything works:

<?php
include 'includes/config.php';
include 'path/to/your/database.class.php';

// Test connection
$conn = database::getConnection();
if ($conn) {
    echo "Database connected successfully!";
}
?>

Common Additional Issues to Check #

• Database User Permissions: Make sure your DB user has permission to access the target database from your server's IP (e.g., if your app is on a remote server, the user needs GRANT ALL ON your_db.* TO 'user'@'your_server_ip';).
• Port Mismatch: If your database uses a non-default port (not 3306), add it as the 5th parameter to the mysqli constructor.
• Firewall/Restrictions: Ensure your server's firewall allows outgoing connections to the database host.

Full Working Class Code #

Here's the complete, improved version of your database class:

<?php
/* 该脚本用于创建数据库连接类 */
include __DIR__ . "/includes/config.php";

class database {
    protected static $connection; // Stores the mysqli connection

    protected function connect(){
        if (!isset(self::$connection)){
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
            try {
                self::$connection = new mysqli(SERVER_NAME, USERNAME, PASSWORD, DB_NAME);
                self::$connection->set_charset('utf8mb4');
            } catch (mysqli_sql_exception $e) {
                error_log('DB Connection Error: ' . $e->getMessage());
                die('Database connection failed. Please try again later.');
            }
        }
        return self::$connection;
    }

    public static function getConnection(){
        $instance = new self();
        return $instance->connect();
    }

    public static function query($sql){
        $connection = self::getConnection();
        try {
            return $connection->query($sql);
        } catch (mysqli_sql_exception $e) {
            error_log('Query Error: ' . $e->getMessage() . ' | SQL: ' . $sql);
            die('Query execution failed.');
        }
    }

    public static function prepare($sql){
        $connection = self::getConnection();
        try {
            return $connection->prepare($sql);
        } catch (mysqli_sql_exception $e) {
            error_log('Prepare Error: ' . $e->getMessage() . ' | SQL: ' . $sql);
            die('Failed to create prepared statement.');
        }
    }
}
?>

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