php with mysql

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array. #!/usr/bin/php <?php $mysqli = new mysqli("localhost", "root", "hidave", "test"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $foo = array(); $query0 = "SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', ...

How do you dig down into the JSON data, say like in comments on a blog post? This is a badly paraphrased question from Peter Zaitsev of Percona at the end of a recent presentation of mine at the Great Wide Open conference. I have to admit I had not done like that with JSON data but it had to be able to be done, right? Surely it can be done. Can't it? Time to dig. I could not find an example of JSON data on a blog with comments. But the good folks at Mongo had an example of a restaurant with grades. { "address": { "building": "1007", "coord": [ -73.856077, 40.848447 ], "street": "Morris Park Ave", "zipcode": "10462" }, "borough": "Bronx", "cuisine": "Bakery", "grades": [ { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 }, { "date": {...

The JSON Functions for using MySQL 5.7 are overwhelming at first glance. A peek at documentation such as the JSON Modification Functions may make you shy at proceeding further. In previous posts the functions to create JSON documents for use in a MySQL column and the functions to get the meta information about JSON documents have been covered. And how to use the built-in JSON functions from PHP have been covered. But now it is time to cover modifying JSON data. Appending Lets start at the beginning and ask the server for a JSON array with the values of 1, 2, 3, 4, and 5. mysql> select JSON_ARRAY(1,2,3,4,5); +-----------------------+ | JSON_ARRAY(1,2,3,4,5) | +-----------------------+ | [1, 2, 3, 4, 5] | +-----------------------+ 1 row in set (0.00 sec) mysql> Using the $ operator to represent our array, we can append an array with the numbers 6, 7, and 8 in it to our array. mysql> SELECT JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8)); +---...

Since I have been presenting on MySQL's JSON data type, I have had questions on how to encode the JSON document into a database column. It has to be a VALID JSON document or the MySQL server will reject it. JSON is a way of storing data for interchange and it consists of two ways of storing data -- key/value pairs called objects and an ordered list of values called an array . To add to this every programming language has its own definitions of what is an object or what is an array, especially PHP. In my early experiments I had a hard time figuring out the various functions needed and am glad to report I was over complicating things. So lets us start with the basics: From JSON.ORG we are told this about objects: An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma). And this about arrays: An array is an ordered collection ...