CODEWITHSUNDEEP
phpmysqliprepared-statement
Glenncito
Glenncito

Reputation: 930

Having trouble executing a SELECT query in a prepared statement

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!

Upvotes: 11

Views: 58430

Answers (3)

DaveRandom
DaveRandom

Reputation: 88647

Just use get_result (instead of bind_result)

// parameters
$date1 = '2012-01-01';
$date2 = '2012-01-31';
$country_code = 'whatever';

// Connect to DB
$db = new mysqli('db', 'username', 'password', 'database');

// The query we want to execute
$sql = "SELECT eventLogID FROM Country WHERE countryCode = ? AND date BETWEEN ? AND ?";

$stmt = $db->prepare($sql);
$stmt->bind_param("sss", $country_code, $date1, $date2); 
$stmt->execute(); 
$result = $stmt->bind_result();

// get results into array
$logIds = $result->fetch_all(MYSQLI_ASSOC);
  
// Do something with the results
print_r($logIds);

Upvotes: 24

Roger Krueger
Roger Krueger

Reputation: 303

Not at all a fan of "bind_result" in mysqli beyond the simplest one-row-expected queries.

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.

Upvotes: 4

Makesh
Makesh

Reputation: 1234

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

Upvotes: 3

Related Questions