问题描述
我遵循了一堆关于在准备好的语句中使用 SELECT 的不同示例，但没有返回任何内容.编辑我已将代码更改为如下所示:
$date1 = 2012-01-01;$date2 = 2012-01-31;$sql_con = new mysqli('db', 'username', 'password', 'database');if($stmt = $sql_con->prepare("SELECT eventLogID FROM Country WHERE countryCode=? AND date BETWEEN ? AND ?")){$stmt->bind_param("sss", $country_code, $date1,$date2);$stmt->execute();$i=0;而 ($stmt-> fetch()){$stmt->bind_result($row[$i]);$i++;}$stmt->close();$sql_con->close();现在所有需要的条目，除了第一个，都被添加到 $row[].为什么不添加第一个条目?提前致谢！
EDIT 07/2015(问题自原始答案以来已被编辑，但基本原则相同)
从不 SELECT * 在生产环境中，它只会回来以奇怪的、不可预测的和看似无关的方式咬你.通过指定所需的列，您将确保列排序、数据类型、约束和各种其他元素从长远来看不会给您带来问题.
这个答案大部分仍然有效，所以我将其保留原样，但主要的收获是:使用 PDO，它可以用更干净、更简洁的方式完成您需要的 98% 的事情API 在同一后端.如果您需要更复杂的特定于 RDBMS 的 API，那么您将已经了解您遇到的问题以及为什么需要 mysqli 等.
<小时>SELECT * 不适用于 MySQLi 准备好的语句.这是我推荐 PDO 的主要原因之一 - 以及绑定变量引用而不是绑定变量引用的荒谬要求参数值.
$stmt->bind_result($row);这不会将结果 row 绑定到变量，它只会绑定单个列.并且因为您使用了 SELECT *，它不会按照您的意愿行事.
如果您确实想在 PDO 上使用 MySQLi(正如我所说，我会推荐)有一些很好的示例说明如何在 SELECT *http://uk3.php.net/manual/en/mysqli-stmt.bind-result.php">bind_result() 手册页上的这个.
或者您可以指定要检索的列:
$sql_con = new mysqli('db', 'username', 'password', 'database');if($stmt = $sql_con->prepare("SELECT name, countryCode FROM Country WHERE countryCode = ?")) {$stmt->bind_param("s", $country_code);$stmt->execute();$stmt->bind_result($name, $countryCode);而 ($stmt-> fetch()) {//因为 $name 和 $countryCode 是通过引用传递的，所以它们的值//每次迭代都会改变以反映当前行echo "";echo "name: $name
";回声国家代码:$国家代码
"；echo "</pre>";}$stmt->close();
<小时>
EDIT 根据您的新代码，您应该这样做:
//$date1 将是 int(2010)，$date2 将是 int(1980) 因为你没有//引用字符串！//$date1 = 2012-01-01;//$date2 = 2012-01-31;//连接数据库$sql_con = new mysqli('db', 'username', 'password', 'database');//在这里检查连接错误！//我们要执行的查询$sql = "选择事件日志ID发件人国家哪里国家代码 = ?和日期之间?和 ?"；//尝试准备查询如果 ($stmt = $sql_con->prepare($sql)) {//传递参数$date1 = '2012-01-01';$date2 = '2012-01-31';$stmt->bind_param("sss", $country_code, $date1, $date2);//执行查询$stmt->execute();如果 (!$stmt->errno) {//这里处理错误}//传递一个变量来保存结果//记住你绑定的是*列*，而不是行$stmt->bind_result($eventLogID);//循环结果并取入数组$logIds = array();而 ($stmt-> fetch()) {$logIds[] = $eventLogID;}//整理$stmt->close();$sql_con->close();//对结果做一些事情打印_r($logIds);} 别的 {//这里处理错误}Ive followed a bunch of different examples regarding using a SELECT in a prepared statement, but nothing is returned. EDIT I have changed my code a bit to look like this:
$date1 = 2012-01-01;
$date2 = 2012-01-31;
$sql_con = new mysqli('db', 'username', 'password', 'database');
if($stmt = $sql_con->prepare("SELECT eventLogID FROM Country WHERE countryCode=? AND date BETWEEN ? AND ?")){
$stmt->bind_param("sss", $country_code, $date1,$date2);
$stmt->execute();
$i=0;
while ($stmt->fetch()){
$stmt->bind_result($row[$i]);
$i++;
}
$stmt->close();
$sql_con->close();
Now all the desired entries, except for the first, are added to $row[]. Why isnt the first entry being added? Thanks in advance!
EDIT 07/2015 (question has been edited since original answer but underlying principles are the same)
Never SELECT * in a production environment, it will only come back to bite you in weird, unpredictable and seemingly unrelated ways. By specifying the columns you want, you will ensure that column ordering, data-type, constraint and all sorts of other elements won't cause you problems in the long run.
This answer is still mostly valid so I'll leave it here as-is, but the main take-away is: use PDO, it does 98% of the things you'll ever need with a much cleaner and more succinct API over the same back end. If you need a more complex RDBMS-specific API then you'll already understand the problems you have and why you need mysqli etc instead.
SELECT * doesn't work very well with MySQLi prepared statements. It's one of the major reasons I recommend PDO instead - that and the ridiculous requirement to bind variable references instead of values to the parameters.
$stmt->bind_result($row);
This is not binding the result row to a variable, it would just be binding a single column. And because you have used SELECT *, it doesn't do what you want it to.
If you do want to use MySQLi over PDO (which, as I say, I would recommend) there are a few good examples of how to SELECT * in the comments like this one on the bind_result() manual page.
Or you can just specify the columns you want to retrieve:
$sql_con = new mysqli('db', 'username', 'password', 'database');
if($stmt = $sql_con->prepare("SELECT name, countryCode FROM Country WHERE countryCode = ?")) {
$stmt->bind_param("s", $country_code);
$stmt->execute();
$stmt->bind_result($name, $countryCode);
while ($stmt->fetch()) {
// Because $name and $countryCode are passed by reference, their value
// changes on every iteration to reflect the current row
echo "<pre>";
echo "name: $name
";
echo "countryCode: $countryCode
";
echo "</pre>";
}
$stmt->close();
EDIT based on your new code, you should be doing this:
// $date1 will be int(2010), $date2 will be int(1980) because you didn't
// quote the strings!
//$date1 = 2012-01-01;
//$date2 = 2012-01-31;
// Connect to DB
$sql_con = new mysqli('db', 'username', 'password', 'database');
// Check for connection errors here!
// The query we want to execute
$sql = "
SELECT eventLogID
FROM Country
WHERE countryCode = ?
AND date BETWEEN ? AND ?
";
// Attempt to prepare the query
if ($stmt = $sql_con->prepare($sql)) {
// Pass the parameters
$date1 = '2012-01-01';
$date2 = '2012-01-31';
$stmt->bind_param("sss", $country_code, $date1, $date2);
// Execute the query
$stmt->execute();
if (!$stmt->errno) {
// Handle error here
}
// Pass a variable to hold the result
// Remember you are binding a *column*, not a row
$stmt->bind_result($eventLogID);
// Loop the results and fetch into an array
$logIds = array();
while ($stmt->fetch()) {
$logIds[] = $eventLogID;
}
// Tidy up
$stmt->close();
$sql_con->close();
// Do something with the results
print_r($logIds);
} else {
// Handle error here
}
这篇关于在准备好的语句中执行 SELECT 查询时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！