mysqli prepare won't output as json

Question

I'm trying to retrieve all records from a sql table and return it as json, however it keep return nothing, even though the sql query return several rows in phpMyAdmin. I've even tried inside the loop to just output all the $id which work fine however when I return it as an array it just return an empty page. I don't know if this can be caused by the $image, which in the database is a blob.

<?php
error_reporting(-1);
ini_set('display_errors', 'On');

include('config.php');
$stmt = $db->prepare('SELECT * FROM camps');
$stmt->execute();
$stmt->bind_result($id, $title, $body, $long, $lat, $img);

$rows = array();
while ($stmt->fetch()) {
  $rows[] = array(
    'id' => $id,
    'title' => $title,
    'body' => $body,
    'long' => $long,
    'lat' => $lat,
    'image' => $img
  );
  echo $img;
}

$stmt->close();
$db->close();
echo json_encode($rows);
?>

Answer 1

Edited: You can't bind on a SELECT * statement unless the number in the bind_result() matches the total number of columns you have in your database. Otherwise, you must choose all the columns and to match the number of columns in your query as in your bind_result(). Also consult the link below on how to show images from a database.

(I've got broad shoulders and will admit my mistake. Nobody learns anything without making any, and I for one have learned something today).

Reference:

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

Having checked for errors in your query, would have thrown you something about it, should it be the case.

The manuals states:

Note that all columns must be bound after mysqli_stmt_execute() and prior to calling mysqli_stmt_fetch(). Depending on column types bound variables can silently change to the corresponding PHP type.

So, what am I not getting here? Maybe they should rewrite it then since they came up with it.

---

Edit: test

After testing what the OP posted for code on my own server running under PHP Version 5.6.21, got back:

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in...

when using SELECT * FROM table

yet did give back a JSON result.

Using SELECT col1, col2 FROM table did not throw that error.

Also, you're trying to echo an image from your database; you can't do that with JSON, you need to use a different method.

See the following question on Stack about how to echo an image from db:

• PHP display image BLOB from MySQL

So your $img would look something like:

echo '<img src="data:image/jpeg;base64,'.base64_encode( $img ).'"/>';

which is something that I have successfully tested with.

• There is something you need to know though, and that is to make sure that each of those rows has an image for them, otherwise there stands to be broken image icons for the ones that don't contain an image (blob).

If that's the case, you would need to limit it to a WHERE clause then.

NOTA: Using blobs can dramatically increase the size of your database. Ideally, saving the files in a folder and its path to it in the database is a method preferred by many. However, that choice is entirely yours.

---

Consult the comments under the answer.

There isn't anything I can add to this.