CODEWITHSUNDEEP
phpmysqlmysqli
mandalorianwarrior
mandalorianwarrior

Reputation: 147

mySQLi is not SELECTing data appropriately

I am converting from mysql_ to mysqli. Doing some tutorials. Not getting much success despite reading and googling the subject. I have attempted a prepared statement but it keeps outputting 0 results (ie. now_rows =0 ). When I manually query the mySQL table there are a number of results that return.

mySQLI 5.0.96 is enabled

<?
include("conn.php");
// SELECT sql query
$sql = "SELECT id, rotation, assignedRad FROM sched_main WHERE thedate = ?"; 
// perform the query and store the result
$query = $conn->prepare($sql);
$thedate='2013-01-02';
$query->bind_param('s', $thedate);
$query->execute();
// if the $query contains at least one row
if ($query->num_rows > 0) {
  // output data of each row from $query
  while($row = $query->fetch_assoc()) {
    echo '<br /> id: '. $row['id']. ' - name: '. $row['rotation']. ' - pass: '.     $row['assignedRad'];
  }
  $query->free();
}
else {
  echo '0 results';
}
?>

Question 2: is there an easy way to debug mysqli? Using mysql_query I would simply echo $sql; to see what the SELECT statement looks like as part of my debugging process. Thanks in advance

UPDATED:

here is a snippet of the updated code as suggested below:

$query->bind_param('s', $thedate);
if (!$query->execute()) {
  die($conn->error);
}
$query->store_result();
// if the $query contains at least one row
if ($query->num_rows > 0) {
  // output data of each row from $query
  while($row = $query->fetch()) {
    echo '<br /> id: '. $row['id']. ' - name: '. $row['rotation']. ' - pass: '. $row['assignedRad'];
  }
  $query->mysqli_free();
}
else {
  echo '0 results';
}

Now it outputs with the array $row values as no value:

id: - name: - pass: 
id: - name: - pass: 
id: - name: - pass:

.. etc...

I also get Fatal error: Call to undefined method mysqli_stmt::mysqli_free()

and when I try free() I get: Fatal error: Call to undefined method mysqli_stmt::free()

Upvotes: 1

Views: 195

Answers (1)

Jordan Kasper
Jordan Kasper

Reputation: 13273

I'm not sure about the debugging part, but you may need to store the result of the DB execution call before calling $query->num_rows. Also, you may want to check if the preparation and execution were successful.

...
if (!$query = $conn->prepare($sql)) {
  die($conn->error);
}
$thedate='2013-01-02';
$query->bind_param('s', $thedate);
if (!$query->execute()) {
  die($conn->error);
}
$query->store_result();
if ($query->num_rows > 0) {
  ...
}

See the PHP doc for more info on num_rows and error

Upvotes: 0

Related Questions