php 在准备好的语句中执行 SELECT 查询时遇到问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11575432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Having trouble executing a SELECT query in a prepared statement
提问by Glenncito
Ive followed a bunch of different examples regarding using a SELECT in a prepared statement, but nothing is returned. EDITI have changed my code a bit to look like this:
我遵循了一堆关于在准备好的语句中使用 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;
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!
现在所有需要的条目，除了第一个，都被添加到 $row[]。为什么不添加第一个条目？提前致谢！
回答by DaveRandom
EDIT07/2015 (question has been edited since original answer but underlying principles are the same)
EDIT07/2015（问题自原始答案以来已被编辑，但基本原则相同）
NeverSELECT *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.
永远不会SELECT *在生产环境中，它只会以奇怪的、不可预测的和看似无关的方式回来咬你。通过指定所需的列，您将确保列排序、数据类型、约束和各种其他元素从长远来看不会给您带来问题。
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.
这个答案大部分仍然有效，所以我将它保持原样，但主要的收获是：使用 PDO，它可以通过更清晰、更简洁的 API 完成您需要的 98% 的事情一样的后端。如果您需要更复杂的 RDBMS 特定 API，那么您将已经了解您遇到的问题以及为什么需要 mysqli 等。
SELECT *doesn't work very well with MySQLi prepared statements. It's one of the major reasons I recommend PDOinstead - that and the ridiculous requirement to bind variable references instead of values to the parameters.
SELECT *不能很好地与 MySQLi 准备好的语句一起使用。这是我推荐PDO的主要原因之一- 以及将变量引用而不是值绑定到参数的荒谬要求。
$stmt->bind_result($row);
This is not binding the result rowto 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.
这不会将结果行绑定到变量，它只会绑定单列。并且因为您使用了SELECT *，它不会按照您的意愿行事。
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 oneon the bind_result()manual page.
如果你想使用的MySQLi比PDO（正如我说的，我会建议）有几个很好的例子如何SELECT *在这样的评论这一块上的bind_result()手册页。
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\n";
echo "countryCode: $countryCode\n";
echo "</pre>";
}
$stmt->close();
EDITbased 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
}
回答by Makesh
I think you have to bind to the columns in bind_results() like
我认为您必须绑定到 bind_results() 中的列，例如
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);
/* fetch values */
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}
Here $col1 and $col2 binds to Code and Name columns of Country table
这里 $col1 和 $col2 绑定到 Country 表的 Code 和 Name 列
(Instead of * in SELECT use the column names)
（而不是 * 在 SELECT 中使用列名）
Further reference : http://php.net/manual/en/mysqli-stmt.bind-result.php
回答by Roger Krueger
Not at all a fan of "bind_result" in mysqli beyond the simplest one-row-expected queries.
除了最简单的一行预期查询之外，一点也不喜欢 mysqli 中的“bind_result”。
Stuffing a whole row into a single array item is better done with:
将整行填充到单个数组项中最好使用：
$stmt->execute();
$result = $stmt->get_result();
while ($data = $result->fetch_assoc())
{
$retvar[] = $data;
}
$stmt->close();
or
或者
while ($data = $result->fetch_row())
if you don't want/need the field names.
如果您不需要/不需要字段名称。
