PHP and MySQL 5.7 JSON


I have been presenting on MySQL 5.7 and the new JSON data type to PHP Programmers at several shows and the one big request has been MORE EXAMPLES!!!!!!

Part of my presentation includes the big differences between PHP’s json_encode and the various MySQL supplied functions like JSON_ARRAY and JSON_OBJECT.  In my preliminary test I found the native PHP function did not always encode the information in a way that the MySQL server found palatable. The MySQL server wants properly valid JSON docs and unfortunately we have an impedance mismatch here.

Lets start with a simple associative array 
$alpha = array('a' => 1, 'b' => 2, 'c' => 3);  
and we want a JSON document that looks like 
{"a": 1, "b": 2, "c": 3}
 If we pass this array through json_encode (echo “json_encoded: ” . json_encode($alpha) . “\n”;)  we will end up with json_encoded: {“a”:1,”b”:2,”c”:3} which is not what is needed and the MySQL server will reject it with the ol’ ‘You have an error in your SQL syntax’ message.  Other php functions like serializeprint_r, and var_dump are pretty good with associative arrays but do not come close to providing the needed valid JSON.

THE JSON_HEX_QUOTE argument to json_encode changes the data to “a”, “1”, “b”, “2”, “c”, “3” }which is not any better. And it is a subtle change that seems, to me at least, to change keys and values to all keys, no values.

With no built in function, it is time to roll the array into a string formatted as desired.

$alphaz = "";
foreach ($alpha as $key => $value) {
                 $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphax,0,(strlen($alphaz) - 2)); 
/* Need to remove last ,\n */

This can be passed to the MySQL JSON_OBJECT function. Since it is valid JSON we can insert it into the database.

INSERT INTO foobar (mydata) VALUES (JSON_OBJECT('a', 1, 'b', 2, 'c', 3))

And in the database is the desired document.


mysql> select * from foobar;
+--------------------------+
| mydata |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

mysql>

I will write more on this subject. Meanwhile feel free to try my little test program.

#!/usr/bin/php
 1, 'b' => 2, 'c' => 3);
echo var_dump($alpha);
echo "json_encoded: " . json_encode($alpha) . "\n";
echo "json_encoded HEX: " . json_encode($alpha,JSON_HEX_QUOT) . "\n";

$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
$query1 = "SELECT JSON_OBJECT($alphaz)";
echo "$query1\n";
if ($result = $mysqli->query($query1)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
        echo "FOO!!!!\n";
}
$query2 = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query2\n";
if ($result = $mysqli->query($query2)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
$query3 = "SELECT * FROM foobar";
if ($result = $mysqli->query($query3)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

$mysqli->close();
?>

2 Responses to PHP and MySQL 5.7 JSON

  1. John Aurelio says:

    Wouldn’t json_encode work just fine if you didn’t wrap the results of it in JSON_OBJECT ?

    Example:
    $a = json_encode(array(‘a’ => 1, ‘b’ => 2, ‘c’ => 3));
    $query = “INSERT INTO foobar (mydata) VALUES ({$a})”;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s