PHP MySQLi Error Handling

In PHP, when working with MySQLi, it's important to handle errors effectively to ensure your application can respond to issues such as failed queries or connection errors. MySQLi offers multiple ways to handle errors, either by checking for errors explicitly or by setting it to throw exceptions.

Podcast on MySQL database management using MySQLi connector

Basic Connection Error Handling

When connecting to a MySQL database using MySQLi, you should check if the connection was successful. If the connection fails, MySQLi can return an error message, which can be captured and displayed.

$connection = new mysqli("localhost", "username", "password", "database");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully";

Output:

Connection failed: Access denied for user 'username'@'localhost'

In this example, $connection->connect_error is used to check if the connection has failed. If an error is found, the script will exit and display the error message.

Handling Errors for Queries

When executing SQL queries, it's important to verify if the query was successful. You can check for query errors using the $connection->error function.

$sql = "SELECT * FROM invalid_table"; // Example of an incorrect query
$result = $connection->query($sql);

if (!$result) {
    echo "Query failed: " . $connection->error;
}

Output:

Query failed: Table 'database.invalid_table' doesn't exist

In this example, we intentionally run an invalid query to demonstrate how errors can be captured using $connection->error.

Using MySQLi Exceptions

Another way to handle errors is by using MySQLi exceptions. You can enable exceptions by using mysqli_report(). This will throw an exception when an error occurs, making it easier to handle errors within a try-catch block.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $connection = new mysqli("localhost", "username", "password", "database");
    $result = $connection->query("SELECT * FROM invalid_table"); // Invalid query
} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage();
}

Output:

Error: Table 'database.invalid_table' doesn't exist

In this example, when an error occurs, an exception is thrown and caught by the catch block. This method of error handling is more structured and fits well into object-oriented programming practices.

Checking for Prepared Statement Errors

When using prepared statements in MySQLi, errors may occur during the preparation or execution phases. These can be captured using $connection->errno and $connection->error.

$stmt = $connection->prepare("INSERT INTO users (name, age) VALUES (?, ?)");
if (!$stmt) {
    echo "Prepare failed: (" . $connection->errno . ") " . $connection->error;
} else {
    $stmt->bind_param("si", $name, $age);
    $stmt->execute();
}

Output:

Prepare failed: (1146) Table 'database.users' doesn't exist

Here, $connection->prepare() checks if the statement preparation was successful. If not, the error code and message are displayed.

Error When Binding Mismatched Data Types in MySQLi

When using MySQLi prepared statements, if the data types of the bound parameters do not match the expected types specified in bind_param(), MySQLi will raise an error. It is crucial to ensure that the types of the variables match the format specifiers in bind_param(), such as
i for integers, s for strings, d for doubles, and b for binary data.

PHP Custom Exception for Marks Validation Without a Separate Class

This example demonstrates how to raise an exception inside an if condition when a student's marks exceed 100, without defining a separate custom exception class. The exception will be handled within a try-catch block to prevent the insertion of invalid data.

PHP Example with Marks Validation

$student_name = "John Doe";
$student_marks = 105; // Invalid marks greater than 100

try {
    // Check if marks are valid
    if ($student_marks > 100) {
        throw new Exception("Marks entered ($student_marks) exceed 100.");
    }

    // Prepare the SQL query to insert the student's record
    $stmt = $connection->prepare("INSERT INTO student (name, marks) VALUES (?, ?)");

    if (!$stmt) {
        die("Prepare failed: (" . $connection->errno . ") " . $connection->error);
    }

    // Bind parameters and execute the statement
    $stmt->bind_param("si", $student_name, $student_marks);
    $stmt->execute();

    echo "Student record added successfully!";
} catch (Exception $e) {
    // Handle the exception for marks exceeding 100
    echo "Error: " . $e->getMessage();
}

Output:

Error: Marks entered (105) exceed 100.

Explanation:

throw new Exception(): Inside the if condition, an exception is thrown if the marks exceed 100, preventing the execution of the insertion query.
try-catch: The try block attempts to insert the record. If the exception is thrown, it is caught in the catch block, where the error message is displayed.
This approach simplifies the code by using the built-in Exception class directly within the condition.

Logging error

The error_log() function in PHP is used to send error messages to a log file, system log, or a custom location. It can be used to track issues without displaying them to users, which is especially useful in production environments.
Syntax:

error_log(message, type, destination, headers)

message: The error message to be logged.
type: Specifies where to log (default is 0 for system log, 3 for custom file).
destination: Specifies the file or URL (for type 3).

Types in the error_log() Function

Type	Description
0	Logs the error message to the PHP system log (usually the web server's error log or syslog).
1	Sends the error message via email to the address specified in the destination parameter.
2	Not used in PHP. Reserved for future use.
3	Appends the error message to a file specified in the destination parameter.

try {
    // Check for connection error
    if ($connection->connect_error) {
        throw new Exception("Connection failed: " . $connection->connect_error);
    }

    // Example SQL query
    $query = "SELECT * FROM non_existent_table";
    if (!$result = $connection->query($query)) {
        throw new Exception("Query failed: " . $connection->error);
    }

} catch (Exception $e) {
    // Log error to PHP error log
    error_log($e->getMessage(), 3, "error_log.txt");

    // Display user-friendly message
    echo "An error occurred. Please check the log file.";
}

try-catch Block: Catches any exceptions during database connection or query execution.
Logging with error_log(): Logs errors into the error_log.txt file for later review.
User Message: Displays a user-friendly message without exposing sensitive error details.

Adding additional information to error log

To include error logging in MySQLi, you can extend the error handling to write errors to a log file, ensuring that issues are tracked even when not displayed on-screen. This can be especially helpful for debugging in production environments.

Here’s how you can log errors in the same style:

$log_file = 'error_log.txt';
$error_message = date('Y-m-d H:i:s') . " - Error: " . $connection->error . "\n";

// Log error to file
file_put_contents($log_file, $error_message, FILE_APPEND);

echo "Error has been logged.";

Conclusion

Handling MySQLi errors in PHP is critical for building robust applications. You can handle errors by checking the connection, queries, and prepared statements or by using MySQLi exceptions. This ensures that your application behaves as expected even in the presence of database issues.
MySQLI database connection file
MySQL DUMP of student table
MYSQLI Functions mysqli_num_rows() Number of rows in result set

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here