php MySQLi 准备语句错误报告

MySQLi prepared statements error reporting

提问by Columbo

I'm trying to get my head around MySQli and I'm confused by the error reporting. I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL, like this:

我正在尝试了解 MySQli，但对错误报告感到困惑。我使用 MySQLi 'prepare' 语句的返回值来检测执行 SQL 时的错误，如下所示：

$stmt_test =  $mysqliDatabaseConnection->stmt_init();
if($stmt_test->prepare("INSERT INTO testtable VALUES (23,44,56)"))
{
 $stmt_test->execute();
 $stmt_test->close();
}
else echo("Statement failed: ". $stmt_test->error . "<br>");

But, is the return value of the prepare statement only detecting if there is an error in the preperation of the SQL statement and not detecting execution errors? If so should I therefore change my execute line to flag errors as well like this:

但是，prepare 语句的返回值是否只检测SQL 语句的准备是否有错误，而不检测执行错误？如果是这样，我应该更改我的执行行以标记错误，如下所示：

if($stmt_test->execute()) $errorflag=true;

And then just to be safe should I also do the following after the statement has executed:

然后为了安全起见，我还应该在语句执行后执行以下操作：

if($stmt_test->errno) {$errorflag=true;}

...Or was I OK to start with and the return value on the MySQLi prepare' statement captures all errors associated with the complete execution of the query it defines?

...或者我是否可以开始并且 MySQLi prepare' 语句的返回值捕获与它定义的查询的完整执行相关的所有错误？

Thanks C

谢谢 C

回答by VolkerK

I wrote this twice before in the last two days (so for me it's a duplicate even though the questions started a bit different).

我在过去两天里写了两次（所以对我来说这是重复的，即使问题开始有点不同）。

Each method of mysqli can fail. You should test each return value. If one fails, think about whether it makes sense to continue with an object that is not in the state you expect it to be. (Potentially not in a "safe" state, but I think that's not an issue here.)

mysqli 的每种方法都可能失败。您应该测试每个返回值。如果失败，请考虑继续处理未处于您预期状态的对象是否有意义。（可能不在“安全”状态，但我认为这不是问题。）

Since only the error message for the last operation is stored per connection/statement you might lose information about whatcaused the error if you continue after something went wrong. You might want to use that information to let the script decide whether to try again (only a temporary issue), change something or to bail out completely (and report a bug). And it makes debugging a lot easier.

因为只有在最后操作的错误信息被存储在每个连接/声明可能会丢失有关的信息是什么，如果你继续后出了问题造成的错误。您可能希望使用该信息让脚本决定是重试（只是临时问题）、更改某些内容还是完全退出（并报告错误）。它使调试更容易。

$stmt = $mysqli->prepare("INSERT INTO testtable VALUES (?,?,?)");
// prepare() can fail because of syntax errors, missing privileges, ....
if ( false===$stmt ) {
  // and since all the following operations need a valid/ready statement object
  // it doesn't make sense to go on
  // you might want to use a more sophisticated mechanism than die()
  // but's it's only an example
  die('prepare() failed: ' . htmlspecialchars($mysqli->error));
}

$rc = $stmt->bind_param('iii', $x, $y, $z);
// bind_param() can fail because the number of parameter doesn't match the placeholders in the statement
// or there's a type conflict(?), or ....
if ( false===$rc ) {
  // again execute() is useless if you can't bind the parameters. Bail out somehow.
  die('bind_param() failed: ' . htmlspecialchars($stmt->error));
}

$rc = $stmt->execute();
// execute() can fail for various reasons. And may it be as stupid as someone tripping over the network cable
// 2006 "server gone away" is always an option
if ( false===$rc ) {
  die('execute() failed: ' . htmlspecialchars($stmt->error));
}

$stmt->close();

edit: just a few notes six years later....
The mysqli extension is perfectly capable of reporting operations that result in an (mysqli) error code other than 0 via exceptions, see mysqli_driver::$report_mode.
die()is really, really crude and I wouldn't use it even for examples like this one anymore.
So please, only take away the fact that each and every(mysql) operation canfail for a number of reasons; even ifthe exact same thing went well a thousand times before....

编辑：六年后的一些笔记......
mysqli 扩展完全能够报告通过异常导致（mysqli）错误代码而不是 0 的操作，请参阅mysqli_driver::$report_mode。
die()真的，真的很粗糙，我不会再用它来做这样的例子了。
因此，请忽略每个（mysql）操作可能由于多种原因而失败的事实；甚至，如果同样的事情进行得很顺利前一千倍....

回答by cmc

Completeness

完整性

You need to check both $mysqliand $statement. If they are false, you need to output $mysqli->erroror $statement->errorrespectively.

您需要同时检查$mysqli和$statement。如果它们为false，则需要分别输出$mysqli->error或$statement->error。

Efficiency

效率

For simple scripts that may terminate, I use simple one-liners that trigger a PHP error with the message. For a more complex application, an error warning system should be activated instead, for example by throwing an exception.

对于可能终止的简单脚本，我使用简单的单行代码触发 PHP 错误消息。对于更复杂的应用程序，应该激活错误警告系统，例如通过抛出异常。

Usage example 1: Simple script

使用示例 1：简单脚本

# This is in a simple command line script
$mysqli = new mysqli('localhost', 'buzUser', 'buzPassword');
$q = "UPDATE foo SET bar=1";
($statement = $mysqli->prepare($q)) or trigger_error($mysqli->error, E_USER_ERROR);
$statement->execute() or trigger_error($statement->error, E_USER_ERROR);

Usage example 2: Application

使用示例2：应用

# This is part of an application
class FuzDatabaseException extends Exception {
}

class Foo {
  public $mysqli;
  public function __construct(mysqli $mysqli) {
    $this->mysqli = $mysqli;
  }
  public function updateBar() {
    $q = "UPDATE foo SET bar=1";
    $statement = $this->mysqli->prepare($q);
    if (!$statement) {
      throw new FuzDatabaseException($mysqli->error);
    }

    if (!$statement->execute()) {
      throw new FuzDatabaseException($statement->error);
    }
  }
}

$foo = new Foo(new mysqli('localhost','buzUser','buzPassword'));
try {
  $foo->updateBar();
} catch (FuzDatabaseException $e)
  $msg = $e->getMessage();
  // Now send warning emails, write log
}

回答by andyface

Not sure if this answers your question or not. Sorry if not

不确定这是否回答了您的问题。对不起，如果没有

To get the error reported from the mysql database about your query you need to use your connection object as the focus.

要从 mysql 数据库报告关于您的查询的错误，您需要使用连接对象作为焦点。

so:

所以：

echo $mysqliDatabaseConnection->error

would echo the error being sent from mysql about your query.

会回显从 mysql 发送的关于您的查询的错误。

Hope that helps

希望有帮助