在准备好的语句中执行 SELECT 查询时遇到问题 [英] Having trouble executing a SELECT query in a prepared statement

问题描述

我遵循了一堆关于在准备好的语句中使用 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(正如我所说，我会推荐)有一些很好的示例说明如何在 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 查询时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！