PHP stmt prepare失败，但是没有错误 [英] PHP stmt prepare fails but there are no errors
问题描述
我正在尝试准备一个mysqli查询，但是它无提示地失败而没有给出任何错误.
I am trying to prepare a mysqli query, but it fails silently without giving any error.
$db_hostname = "test.com";
$db_database = "dbname";
$db_username = "db_user";
$db_password = "password";
$db = new mysqli($db_hostname,$db_username,$db_password,$db_database);
$q = "INSERT INTO Members (`wp_users_ID`,`MemberID`,`Status`,`MiddleName`,`Nickname`,`Prefix`,`Suffix`,`HomeAddress`,`City`,`State`,`Zip`,`ExtendedZip`,`BadAddress`,`SpouseFirstName`,`SpouseMiddleName`,`HomePhone`,`CellPhone`,`WorkPhone`,`WorkPhoneExt`,`OfficePhone`,`OfficePhoneExt`,`Pager`,`Fax`,`Company`,`CompanyType`,`OfficeAddress`,`OfficeAddress2`,`OfficeCity`,`OfficeState`,`OfficeZip`,`OTYPECO`,`OSTAG`,`UPCODE`,`Region`,`Department`,`Classification`,`Retired`,`Industry`,`Comments`,`Officer`,`OfficerType`,`OfficerTitle`,`OUNIT`,`ReceiveEMagazine`,`CD`,`SD`,`AD`,`isOrganization`,`DEL`,`Dues`,`DataSource`) VALUES ((?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?));";
$stmt = $db->prepare($q);
if ( false === $stmt ) {
echo "<pre>";
print_r( $db );
echo "</pre>";
mysqli_report(MYSQLI_REPORT_ALL);
echo mysqli_error();
}
唯一实际显示任何内容的部分是print_r($ db):
The only part that actually shows anything is print_r( $db ):
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.1.73
[client_version] => 50173
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[error_list] => Array
(
)
[field_count] => 1
[host_info] => dbhost.com via TCP/IP
[info] =>
[insert_id] => 919910
[server_info] => 5.1.73-log
[server_version] => 50173
[stat] => Uptime: 1924325 Threads: 8 Questions: 642600129 Slow queries: 28158 Opens: 24168750 Flush tables: 1 Open tables: 403 Queries per second avg: 333.935
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 9939810
[warning_count] => 0
)
有人看到会导致这种情况的东西吗?没有任何错误，很难看出问题出在哪里……我尝试将结果查询直接复制并粘贴到phpmyadmin中，并且运行得很好(在用测试值手动替换问号之后).
Does anyone see anything that would cause this? Without any errors, it's difficult to see what is wrong... I tried copying and pasting the resulting query directly into phpmyadmin and it ran just fine (after manually substituting the question marks with test values).
谢谢！
更新
看来，由于在页面顶部添加了 mysqli_report(MYSQLI_REPORT_ALL); ，现在插入查询之上的查询现在失败了，尽管仍然没有给出错误.这一次执行失败:
It appears that since adding mysqli_report(MYSQLI_REPORT_ALL); to the top of the page, a query ABOVE the insert query is failing now, though still no error is given. This one is failing on execute:
echo "1";
$idDataSources = "";
echo "2";
$q = "SELECT idDataSources FROM DataSources WHERE `description`=(?);";
echo "3";
$stmt = $db->prepare($q);
echo "4";
$stmt->bind_param('s',$description);
echo "5";
$description = "File - 01/10/2015";
echo "6";
$stmt->execute() or die( mysqli_stmt_error( $stmt ) );
echo "7";
$stmt->bind_result($idDataSources);
echo "8";
$stmt->fetch();
echo "9";
unset($params);
输出:
123456
它进入$ stmt-> execute()并失败.再一次，我尝试输出错误，但是什么也没有显示.这真是莫名其妙.我想知道我是否应该恢复到旧的mysql(非面向对象)方法...这是不安全的方法，但至少它能够始终如一地工作，并在出现问题时显示错误.
It gets to $stmt->execute() and fails. Once again, I tried outputting the error, but nothing shows up. This is really baffling. I'm wondering if I should revert back to the old mysql (non object oriented) method ... it was insecure, but at least it worked consistently and showed errors when something was wrong.
更新2
好吧，我只是使用mysql(非面向对象)而不是mysqli重写了整个脚本...就像梦一样.我希望我可以切换到较新的标准，但是由于存在这样的随机故障和错误报告错误，因此这确实很困难.我将保留更好"的版本，直到我弄清楚它为什么失败为止.
Well, I just rewrote the entire script using mysql (non object oriented) instead of mysqli ... works like a dream. I wish I could switch to the newer standards, but with random glitches and poor error reporting like this, it sure is difficult. I'll shelf the "better" version until I can figure out why it fails.
更新3
我注意到mysqli有一个有趣的行为.在同一代码中的其他地方，我有两个通过STMT运行的查询，一个接一个.这偶尔会失败.这些失败并不一致，因为我可以提交50次相同的数据，而其中的20次可能会失败...相同的数据，相同的功能.
I noticed an interesting behavior with mysqli. Elsewhere in the same code I have two queries running through STMT one after the other. This was failing every once in a while. The failures were not consistent as I could submit identical data 50 times and out of those, it might fail 20 times... same data, same function.
为了准确地确定脚本错误的出处，我在两个查询的每个语句之间插入了echo命令，只是吐出一个数字以查看计数的停止位置-事实证明，使用不相关的命令，它会降低了STMT的速度，使其可以持续工作.这使我想知道STMT连接是否没有正确关闭.
In an attempt to identify exactly where the script was erroring out, I put in echo commands between each statement in both queries, just spitting out a single number to see where the count stops - turns out that with the unrelated commands, it slowed STMT down just enough that it works consistently. This lead me to wonder if maybe the STMT connection is not properly closing.
$q = "";
$stmt = $this->db->prepare( "SELECT ID FROM Members WHERE MemberID='5' LIMIT 1;" );
$stmt->execute();
$stmt->store_result();
if ( $stmt->num_rows > 0 ) {
$q = "UPDATE Members SET Name='Test' WHERE MemberID=(?) LIMIT 1;";
}
$stmt->close();
// here if we continue, it has a chance of erroring out. However,
// if we run just the following command instead, everything works perfect.
//
// mysql_query( "UPDATE Members SET Name='Test' WHERE MemberID='5' LIMIT 1;" );
if ( $q != "" ) {
$stmt = $this->db->prepare($q);
$stmt->bind_param('i',$params['ID']);
$params['ID'] = 5;
$stmt->execute();
$stmt->close();
unset($params);
}
有人可以解释这种行为吗?似乎它们永远不会发生冲突，因为我在开始新查询之前就使用了close()命令，并且它在某些时候确实有效……似乎很奇怪.
Can anybody explain this behavior? It doesn't seem like they should ever be conflicting since I am using the close() command before starting a new query, and it DOES work SOME of the time... seems bizarre.
推荐答案
这是来自php.net的稍有改动的示例脚本，带有错误处理:
Here is a slightly adapted example script from php.net with error handling:
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT idDataSources FROM DataSources WHERE `description`=(?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$description = "File - 01/10/2015";
if (!$stmt->bind_param('s', $description)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
/* explicit close recommended */
$stmt->close();
?>
请注意，$ mysqli 或 $ stmt都可以保存错误描述.
Please note that either $mysqli or $stmt can hold the error description.
这篇关于PHP stmt prepare失败，但是没有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
