php with mysql

JSON in Flat File to MySQL Database So how do you load that JSON data file into MySQL. Recently I had this question presented to me and I thought I would share a handy script I use to do such work. For this example I will use the US Zip (postal) codes from JSONAR. Download and unzip the file. The data file is named zips.json and it can not be bread directly into MySQL using the SOURCE command. It needs to have the information wrapped in a more palatable fashion. head zips.json { "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" } { "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" } { "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "stat...

The Video The find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects ( kidding! ), and the amount of the content. The Script For those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above). \connect root@localhost/world_x db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find() . But that returns 237 JSON documents, too many! So lets cut it down to one record by qualifying that we only want the record where the _id is equal to USA , db.countryinfo.find(‘_id = “USA”’) Deeper Level items in the Document You can reach deeper level items by providing the path to the object such a...

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL. The first Episode, Introduction, can be found here. Please provide feedback and let me know if there are subjects you would want covered in the near future.

JSON Data Into MySQL JSON is now a very hot format for sharing data and MySQL's 5.7 Data Set is now a very hot way of storing that data. But I have had some requests on getting a raw JSON data set into MySQL. So lets start with Global Airfields data from the Awesome JSON Datasets collection. airfields.json The data is in a file named airfields.json and comes as one very long line. MySQl has now way of taking out the individual JSON documents from that string and putting them in a row. The jq tool will let use see the data broken down into objects. Try jq -C '.[]' airfields.json and you will see individual documents colorized. { "ID": "LFOI", "ShortName": "ABBEV", "Name": "ABBEVILLE", "Region": "FR", "ICAO": "LFOI", "Flags": 72, "Catalog": 0, "Length": 1260, "Elevation": 67, "Runway": "0213...

Is it cheating? Is using MySQL without Structured Query Language (SQL) or putting all your data into one column proper? Impossible a year ago and probably thought as a poor/crazy practice until recently, this is a new type of MySQL usage. NoSQL has had a big impact in the SQL world with several relational products from vendors like MySQL, Microsoft, Postgresql and others offering NoSQL interfaces and JSON data types to their databases. Several old timers have come to me asking if putting lots of data in a JSON column is cheating? After all data normalization is part of relational databases and the way to efficiency and speed is well organized data. This higgledy–piggledy fashion of putting an entire document in a column without breaking it down to its component sections does violate the first rule of data normalization. And that has worked for decades pretty well. But things change. Many new development projects start with no idea what their data is going to look like. Maybe, if t...

The MySQL Document Store introduced with version 5.7.12 allows developers to create document collections without have to know Structured Query Language. The new feature also comes with a new set of terminology. So let us create a collection and see what it in it (basically creating a table for us SQL speakin' old timers). So start the mysqlsh program, connect to the server, change to the world-x schema (database) switch to Python mode, a create a collection (table). What did the server do for us? Switching to SQL mode, we can use describe to see what the server has done for us. We have a two column table. The first is named doc and is used to store JSON. And there is also a column named _id and please notice this column is notated as STORED GENERATED. The generated column extracts values from a JSON document and materializes that information into a new column that then can be indexed. But what did the system extract for us to create this new column? Lets use SHOW CREATE TAB...

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\"}', ...