1

How do I get the data from a JSON type field using mysqli prepared statements that use bind_result() / fetch()? Or in the table do I have to change that JSON type field to a text type field?

If the field type is JSON, all I get from my prepared statements below is NULL. Everything else is working fine. The mysql version is 5.7.30 and PHP is 5.6.40.
My host does not have mysqlnd installed, so I'm unable to use get_result() and fetch_assoc().

Here's the code I would like to get working:

<?php
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = 'SELECT id, text, json FROM test WHERE id = 1';

$stmt = $db->prepare($query);
$stmt->execute();

$data = [];
$stmt->bind_result($data["id"], $data["text"], $data["json"]);
while ($stmt->fetch()) {
    $row = [];
    foreach ($data as $key => $val) {
        $row[$key] = $val;
    }
//  $array[] = $row;
}
var_dump( $row );       //  Array of first row

/* Output

array(3) {
  ["id"]=>
  int(1)
  ["text"]=>
  string(7) "Chart 1"
  ["json"]=>
  NULL
}

*/

$stmt -> close();

Using the less secure query() / mysqli_fetch_assoc() way of accessing the same $query, I'm able to get the data from the JSON type filed without any problem:

<?php
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = 'SELECT id, text, json FROM test WHERE id = 1';

$result = $db->query($query);
$row = mysqli_fetch_assoc($result);
var_dump( $row );       //  Array of first row

/* Output

array(3) {
  ["id"]=>
  string(1) "1"
  ["text"]=>
  string(7) "Chart 1"
  ["json"]=>
  string(21) "{"a": 0, "p": "asdf"}"
}

*/

Here's the code that creates the table 'test' for the above:

<?php
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if ($db->connect_errno)
    echo "Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error;

if (!$db->query("DROP TABLE IF EXISTS test") ||
    !$db->query("CREATE TABLE test(id INT, text TEXT, json JSON)") ||
    !$db->query("INSERT INTO test(id, text, json) VALUES (1, 'Chart 1', '{\"p\":\"asdf\",\"a\":0}')")) {
    echo "Table creation failed: (" . $db->errno . ") " . $db->error . "\r";
}

I came across this issue while converting mysqli query() / mysqli_fetch_assoc() code into the more SQL injection resistant prepared statements. I've managed to get it all working except for this json-field-returns-null problem. I could only find this mention of a JSON field in relation to using mysqli prepared statements: Commands out of sync; you can't run this command now

SKisby
  • 35
  • 7
  • Humour me and try `$stmt->bind_result($id, $text, $json); $stmt->fetch(); var_dump($id, $text, $json);` – Phil Jul 15 '20 at 10:14
  • 1
    If you are still using PHP 5 I strongly recommend to upgrade as soon as possible. This version is no longer supported. [Let Rasmus Lerdorf explain it to you](https://youtu.be/wCZ5TJCBWMg?t=2434) – Dharman Jul 15 '20 at 10:33
  • 2
    If you are converting your code then it would be much easier if you converted straight to PDO instead of having to deal with mysqli. PDO is much easier. If your host doesn't offer you modern PHP version then **it's time to change host!** – Dharman Jul 15 '20 at 10:35

0 Answers0