php using mysqli convert resultset to an array and then to json

Question

I thought what I want is simple but for some reason, I'm stuck. I have the following:

$sql = "...";
if ($stmt = $con->prepare($sql)) {          
   $stmt->bind_param("sss", $x,$y,$z);
   if ($stmt->execute()) {
      $result = array(); //not sure if needed
      $stmt->bind_result($x1,$y1,$z1); //not sure if needed
      //loop resultset and put it in an array ($result);
      echo json_encode($result); // convert to json
      $stmt->close();
   }
}

I saw fetchAll, fetch_assoc and much more but I keep getting errors for these calls/functions as undefined. Other examples were for non prepared statements. No matter what I've tried, I failed to create an array with the resultset, what am I missing?

Thanks

Answer 1

After you use bind_result, you still need to fetch those:

$sql = "SELECT col1, col2, col3 FROM table_name WHERE col4 = ? AND col5 = ? AND col6 = ?";
if ($stmt = $con->prepare($sql)) {          
   $stmt->bind_param('sss', $x,$y,$z);
   if ($stmt->execute()) {
      $result = array();
      $stmt->bind_result($x1,$y1,$z1);

      // You forgot this part
      while($stmt->fetch()) {
          $result[] = array('col1' => $x1, 'col2' => $y1, 'col3' => $z1);
      }

      echo json_encode($result); // convert to json
      $stmt->close();
   }
}

Or if you have mysqlnd driver on the system:

$sql = "SELECT col1, col2, col3 FROM table_name WHERE col4 = ? AND col5 = ? AND col6 = ?";
if ($stmt = $con->prepare($sql)) {          
   $stmt->bind_param('sss', $x,$y,$z);
   if ($stmt->execute()) {
      $data = $stmt->get_result();
      $result = array();

      while($row = $data->fetch_assoc()) {
          $result[] = $row;
      }

      echo json_encode($result); // convert to json
      $stmt->close();
   }
}