what is the best way to parse big JSON file and insert into database using php?

Question

I have this huge JSON file. Currently the way I am using it is:

1. Read the entire contents into a string
2. Do json_decode, get the array
3. Loop through the array one record at a time and build my SQL insert statement

Problem is, the code is ugly. Also, some of the objects in these arrays are themselves arrays, and not all records contain all values. I have to use isset to check if a particular value is present, or use a default value etc. Overall, it works correctly, but the code is ugly. Is there any way I can write this better?

Answer 1

Beauty is in the eye of the beholder.

If the format of the JSON is beyond your control then there's not much you can do except shuffle the pieces around the board. Do the usual thing of making your functions as small and neat as possible instead of having one large block of code. Multiple function will be easier to read and more reusable.

I tend to call this process functional decomposition.

Of course you can also take an OO approach and build up some nice objects that save themselves to the database and build themselves from pieces of the JSON object but this is basically the same thing (except it makes OO purists sleep better at night).

Answer 2

Hard to advice without looking at the code, but I'd like to remind you about the PHP + operator for arrays, which merges two arrays non-destructively.

$default_values = array('Name' => '', 'Email' => '', 'Meta' => '');
$data = array('Name' => 'John'); // Only name set
$fixed_data = $data + $default_values;

$fixed_data now looks like array('Name' => 'John', 'Email' => '', 'Meta' => ''); without a need for multiple isset() checks. (Might not be applicable to your case, hard to say without more info.)

Answer 3

Most databases (MySQL, sqlite etc.) prefer to work with the XML data format to import/export data.

You could convert your JSON object into XML, and then work with that.

I'm not surprised to see databases supporting JSON in the near future, but for now this is probably the best thing to do.

Answer 4

If you use a beautiful database abstraction like ADODb, then you will net need to build the insert query anymore.

This is the example of insert using ADODb:

$data['col1'] = 'value1';
$data['col2'] = 'value2';
$data['col3'] = 'value3';

$result = $adodb->AutoExecute($tablename, $data, 'INSERT');

Since the result of json_encode already an array, it fit perfectly. If the column definition in the database allow null, then the a missing column in $data will still be a valid insert.

Answer 5

If you dont want to write ugly code to generate sql queries consider using an ORM Propel and doctrine are the ones i have personally used.

doctrine has a method to generate an object from an array and then simply call a save method on it.

have a look at this http://www.doctrine-project.org/documentation/manual/1_0/en/working-with-models:arrays-and-objects:from-array