How to print SQL statements and result object contents when an error occurs

In PHP, when using the mysqli extension to interact with a MySQL database, we often use the mysqli_result function to process query results. In order to ensure that any errors can be caught when executing SQL queries and view the contents of SQL statements and result objects when the error occurs, we usually perform some additional debugging output when the error occurs.

In this article, we will show how to capture errors and print the contents of SQL statements and query result objects when executing a query.

Step 1: Create a database connection

First, we need to create a connection to the MySQL database. Here we use mysqli_connect to connect to the database server.

 <?php
$host = "localhost";  // Database host address
$username = "root";   // Database username
$password = "";       // Database Password
$dbname = "my_database";  // Database name

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

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

Step 2: Execute the query and process the results

When executing SQL queries, we can capture the return value of the mysqli_query function. If a query error occurs, we will print out the SQL statement and the returned result object content.

 <?php
// Example SQL Query
$sql = "SELECT * FROM users"; // Query users All records in the table

// 执行Query
$result = $conn->query($sql);

// 检查Query是否成功
if (!$result) {
    // 如果Query失败，Print error message
    echo "Query失败: " . $conn->error . "<br>";
    
    // Print out the executed SQL Statement
    echo "SQL Statement: " . $sql . "<br>";
    
    // Print mysqli_result Object content（If so）
    echo "结果Object content: ";
    var_dump($result);
} else {
    // 如果Query成功，Processing results
    while ($row = $result->fetch_assoc()) {
        echo "userID: " . $row["id"] . " - user名: " . $row["username"] . "<br>";
    }
}
?>

explain

1. $conn->query($sql) : This line of code is used to execute SQL queries. If the query fails, $result will be false , and we can print out the error message and SQL statement.

2. $conn->error : This property returns the most recently executed SQL query error message. We can use it to diagnose the cause of query failure.

3. var_dump($result) : If the query fails, the $result variable will be false . For debugging, we use var_dump to output the content of the mysqli_result object to help us view the specific information when an error occurs.

Step 3: Close the database connection

After the operation is completed, we should close the database connection.

 <?php
$conn->close();
?>

Summarize

Through the above code example, when the SQL query fails, the program will print out failed SQL statements and error messages, helping developers debug and locate problems. Especially during the development and testing phases, capturing and outputting this information is very useful, ensuring that we quickly find the problem in the query.

If you are developing a more complex database application, it is recommended to perform error handling and debug output every time a query is executed, especially if the query statement may be errored due to dynamic input or other factors.

I hope this article will be helpful to you, and you are welcome to ask more questions at any time!