Having trouble executing a SELECT query in a prepa

Question

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!

Answer 1

I think you have to bind to the columns in bind_results() like

/* 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

(Instead of * in SELECT use the column names)

Further reference : http://php.net/manual/en/mysqli-stmt.bind-result.php

Answer 2

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\n";
     echo "countryCode: $countryCode\n";
     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
}