To check errors in PHP preprocessing statements, you must enable the error reporting mechanism first. 1. When using PDO, set $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION), or use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) to enable mysqli error reporting; 2. Check the return values ??of prepare() and execute(), and if it fails, output specific error information; 3. Ensure that the number and type of parameters in bind_param match, i represents an integer, d represents a double, s represents a string, and b represents a blob; 4. Use logging or debugging tools such as Xdebug to view SQL and variable content to assist in troubleshooting.

It is actually not difficult to check errors in PHP preprocessing statements, but many people tend to ignore some key points when writing code, which leads to errors and don’t know what the problem is. To ensure that preprocessing statements do not make any errors, the key is to check the return value of each step and enable the error reporting mechanism .

1. Enable MySQL Error Reporting (PDO or mysqli)

If you are using PDO , be sure to set the error mode when connecting to the database:

 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In this way, once an SQL execution error occurs, the program will throw an exception, and you can see which step is wrong.

If you are using mysqli , you can manually turn on the switch that reports the error:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

After adding this line, as long as mysqli operation error occurs, it will directly send an error instead of failing silently.

2. Check whether prepare() and execute() succeed

Whether it is PDO or mysqli, you must check the return values ??of prepare() and execute() .

For example, using mysqli:

 $stmt = $mysqli->prepare("SELECT id FROM users WHERE email = ?");
if ($stmt === false) {
    die('Prepare failed: ' . $mysqli->error);
}

if (!$stmt->bind_param("s", $email)) {
    die('Binding parameters failed: ' . $stmt->error);
}

if (!$stmt->execute()) {
    die('Execute failed: ' . $stmt->error);
}

There is a judgment added here at each step, and if it fails, an error message will be output. Especially prepare() , if SQL is written incorrectly or the table name and field name are spelled incorrectly, it will not automatically report an error, and you must check the return value by yourself.

3. Check whether the binding parameters match

A common problem is that the type and number of bound parameters are inconsistent . For example:

• The placeholder is ? but bind_param passes multiple variables;
• The type character is incorrect, such as "s" used in integers;
• Forgot to pass a variable in.

These will cause execute() to fail, but sometimes the error message may not be clear enough. The recommended approach is:

• To check how many variables are there in SQL ? it is necessary to correspond to several variables;
• Ensure that the type characters are correct: i is an integer, d is a double, s is a string, and b is a blob;
• If you are not sure, you can print SQL out and see if the structure is correct.

4. Use debugging tools or logging

Sometimes the error does not appear in the code, but the data itself is problematic. At this time, you can use logging to print out SQL and parameters for easy troubleshooting.

for example:

 error_log("SQL: SELECT id FROM users WHERE email = ? with email: $email");

Or to be more advanced, use debugging tools (such as Xdebug) to view variable content to quickly locate problems.

Basically that's it. As long as you develop the habit, check the return value every time, turn on error reports, and pay attention to parameter binding, problems in preprocessing statements can basically be discovered in a timely manner.

The above is the detailed content of How to check for errors in PHP prepared statement. For more information, please follow other related articles on the PHP Chinese website!