Common problems and solutions to PHP database connections

Common problems and solutions for PHP database connections are: connection failure: check connection information and MySQL service status; query failure: check query syntax, tables and fields, and connection validity; insertion, update, and delete failures: check SQL Statements, target tables and fields, and connection validity; database connection leaks: explicitly close the connection or use a try...catch...finally block.

Common problems and solutions to PHP database connections

1. Connection failure

Problem 1: mysqli_connect() Returns false

$conn = mysqli_connect("localhost", "username", "password", "database");
if (!$conn) {
    echo "连接失败：" . mysqli_connect_error();
}

Solution:

• Check the host name , user name, password and database name are correct.
• Make sure the MySQL server is running.
• Check that PHP has MySQL extension enabled.

Problem 2: PDO::__construct() throws an exception

$dsn = "mysql:host=localhost;dbname=database";
$conn = new PDO($dsn, "username", "password");

Solution:

• Check whether the connection string is correct.
• Make sure the PDO MySQL driver is enabled.
• Check that PHP has MySQL extension enabled.

2. Query failed

Question 1: mysqli_query() Returns false

$query = "SELECT * FROM users";
$result = mysqli_query($conn, $query);
if (!$result) {
    echo "查询失败：" . mysqli_error($conn);
}

Solution:

• Check whether the query syntax is correct.
• Make sure the table and fields exist.
• Check whether the connection is valid.

Problem 2: PDOStatement::execute() throws an exception

$stmt = $conn->prepare($query);
$stmt->execute();

Solution:

• Check whether the query parameters are correct.
• Check whether the connection is valid.
• Make sure there are no errors in the query results.

3. Insertion, update, and deletion failed

Problem: mysqli_affected_rows() or PDOStatement:: rowCount() returns 0

Solution:

• Check whether the SQL statement is correct.
• Make sure the target table and fields exist.
• Check whether the connection is valid.

4. Other problems

Problem: Database connection leakage

Solution:

• Use mysqli_close() or PDO::close() to explicitly close the connection.
• Use PHP's try...catch...finally block to ensure that the connection is closed in any case.

Practical case

Connect to MySQL database

// PHP >= 8.0，推荐使用 PDO
$dsn = "mysql:host=localhost;dbname=database";
$conn = new PDO($dsn, "username", "password");

// PHP < 8.0，使用 mysqli_connect()
$conn = mysqli_connect("localhost", "username", "password", "database");

Query database

// PDO
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$result = $stmt->fetchAll();

// mysqli
$query = "SELECT * FROM users WHERE id = " . $id;
$result = mysqli_query($conn, $query);
$result = mysqli_fetch_all($result);

The above is the detailed content of Common problems and solutions to PHP database connections. For more information, please follow other related articles on the PHP Chinese website!