JSON output for SELECT with Prepared Statement

Question

I am trying to encode the results from SELECT query with prepared statement into JSON output. I have the following code but I can't get it working. Any help would be greatly appreciated.

$query = "SELECT Item.ItemID, Item.ItemName FROM Items"
    $stmt = $db->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $numrows = $stmt->num_rows;
    $stmt->bind_result($ItemID, $ItemName);

 for ($i=0; $i <$numrows; $i++) {
    $stmt->fetch();

$JSONArray = ["ItemID" => $ItemID,
             "ItemName" => $ItemName
             ]

echo json_encode($JSONArray);
}

there is a missing quotation in the code ($query doesnt have the second quote) , if this was not the problem please write what is the error displayed to you — odai, Oct 09 '16 at 18:23
Please add further information, how is it not working, do you get any errors? — JimL, Oct 09 '16 at 18:23

score 2 · Answer 1 · answered Oct 09 '16 at 18:26

You should always add the items to the container array, not overwrite the whole array and encode & echo only once:

...

$JSONArray = []; // initialize to empty array
for ($i=0; $i <$numrows; $i++) {
    $row = $stmt->fetch();
    $ItemID = $row['ItemID'];
    $ItemName = $row['ItemName'];
    // add the new item in each iteration
    $JSONArray[] = ["ItemID" => $ItemID,
             "ItemName" => $ItemName
             ];
}
echo json_encode($JSONArray);

score 1 · Answer 2 · answered Oct 09 '16 at 18:26

Don't you think it should be Items not Item ? also there's missing quote and semicolon.

$query = "SELECT Items.ItemID, Items.ItemName FROM Items";
$stmt = $db->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll();

echo json_encode($result);

Also for convention I'd suggest you to keep lowercase table names. :)

score 0 · Answer 3 · answered Feb 09 '20 at 17:08

When creating the PDO Object add:

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")

e.g.:

$db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

that helped in my case : )

as seen on https://stackoverflow.com/a/7030557/5764766

JSON output for SELECT with Prepared Statement

3 Answers3