Solved

Save JSON data in mySQL database

Posted on 2013-06-11
13
683 Views
Last Modified: 2015-07-04
I have the result from an API I would like to cache in a mySQL DB

I will have two tables, one with the parameter used to call the API and one with the rows of results


My idea is the following

<?php
  $apiCallId=$_POST["id"]; // foreign key in row DB

  /* json contains
    {
      "someHeader": { ... },
      "items":[
        { "someObject": { "someNestedObject": { .. } },
        { "someObject": { "someNestedObject": { .. } },
        { "someObject": { "someNestedObject": { .. } }
      ]
    }
   */
  $phpObj = json_decode(someAPICall(....)); // get the JSON - not sure I need to decode

  $items=$phpObj->items;
  for ($i=0;$i<count($items),$i++) {
    // here I need to add each item to the DB so I get
    // $id - 0 - { "someObject": { "someNestedObject": { .. } }
    // $id - 1 - { "someObject": { "someNestedObject": { .. } }
  }
?>  

Questions

1. Do I have to decode the JSON or can PHP read the JSON and access each array item from items
2. I am pretty sure I need to B64 encode the item data, e.g. each { "someObject": { "someNestedObject": { .. } }
3. Can I concatenate the sql so I only do one commit? I expect 10 items, but do not know the size of them.

I then later need to reverse the process so I can deliver an
{ "items":[ {....},{....}] }
to the browser

I would love some hand holding on the php syntax. I have access to mysqli (and even PDO) if needed but have no experience with either
0
Comment
Question by:Michel Plungjan
13 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39238836
I may be missing something but I would create only one table with an autoincrement 'id' field, the api-key field and the base64-encoded JSON field.  Note that by base64 encoding the data, you can not search the table for the original contents.  If you need to do that, then you need to decode the JSON to store it in the table so you can find things.  It does not appear that PHP can directly access the JSON data without decoding it.
http://us1.php.net/manual/en/book.json.php - http://us1.php.net/manual/en/function.json-decode.php

The mysqli examples on php.net are pretty clear and most of the time all I have to do is use my own variable names in place of the examples.  http://us1.php.net/manual/en/book.mysqli.php
0
 
LVL 82

Expert Comment

by:hielo
ID: 39239100
>>1. Do I have to decode the JSON or can PHP read the JSON and access each array item from items
If I understand you correctly, the API is returning a JSON formatted string and you are interested to iterate over "items" and in doing so you want to then save each item as a JSON string in the DB.

If that is the case, you need to decode so that "items" can become "iterable."  This "decoding" process will decode everything, including each item in "items".  Thus, within the for construct you need to encode each "item" (singular) so that you can then save THAT specific item as a json-encoded string.

>>2. I am pretty sure I need to B64 encode the item data, e.g. each { "someObject": { "someNestedObject": { .. } }
If you need to "search" for data within that field, then I would stay away from B64 encoding.

>>3. Can I concatenate the sql so I only do one commit? I expect 10 items, but do not know the size of them.
MySQL certainly supports the ability to execute:
INSERT INTO Table(FieldName) Values ('first value'), (' second value'), ('third value'), etc

But then you need to make sure you escape the values in order to avoid sql injection. My suggestion is to use a prepared statement instead.  On each iteration you just update the value you want to insert and execute it.  The API does the escaping under the hood for you.

Read the comments in the code below.
<?php

$phpObj = json_decode(someAPICall(....)); // get the JSON - not sure I need to decode 
if( is_null($phpObj) )
{
	echo 'Unable to decode data';
}
else
{
	$items=$phpObj->items; 

	/* Connect to an ODBC database using driver invocation */
	$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
	$user = 'dbuser';
	$password = 'dbpass';
	$errors=array();

	try {
    	$dbh = new PDO($dsn, $user, $password);
		//force PDO to throw exceptions when errors are encountered
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$dbh->beginTransaction();

		try
		{
			/* Execute a prepared statement by passing an array of values */
			$sth = $dbh->prepare('INSERT INTO tablename(fieldName) VALUES(?)');

			for ($i=0, $limit=count($items); $i<$limit; ++$i)
			{
				// here I need to add each item to the DB so I get
				// $id - 0 - { "someObject": { "someNestedObject": { .. } }
				// $id - 1 - { "someObject": { "someNestedObject": { .. } }
				$item=json_encode($items[$i]);
        
				//if an error were to occurr while executing the command, an
				//exception will be thrown (due to the setting specified 
				//above immediately after connecting to the db).  So you don't need
				//to check if the statement succeeded or failed.  It will be caught
				//in the "catch" clause.
				$sth->execute( array($item) );

				//if you have an auto-id column, you may be instersted in the
				//following statement
				//echo '<br />Id of inserted item: '.$dbh->lastInsertId();
			}
			//if you make it this far, no exceptions were thrown - everything was OK
			$dbh->commit();
    	}
		catch(PDOException $e)
		{
			//if you make it here, there were problems in the prepare() OR execute() method calls.
			$dbh->rollBack();
			echo "Error!: " . $e->getMessage() . '<br />'; 
		}

	}
	catch (PDOException $e)
	{
		echo 'Connection failed: ' . $e->getMessage();
	}
}
?>

Open in new window


Regards,
Hielo
0
 
LVL 75

Author Comment

by:Michel Plungjan
ID: 39239147
I need to have two tables
The first is to present the saved query parameters from the original api call, the other to simply store the 10 rows returned.

So what I seem to need is

1) store the parms in master table and create a unique ID
2) call the api and decode into a php object
3) run over object->items and for each item save it

So what would the php and sql statement look like?

$items=$phpObj->items;
for ($i=0;$i<count($items),$i++) {
    $query = "INSERT INTO 'cache' (id, idx, b64json) VALUES ($id,$i,'base64_encode(json_encode($items[$i]))')";    
    $result = $mysqli->query($query);
  }

or can I do something more clever?
0
 
LVL 82

Expert Comment

by:hielo
ID: 39239184
Refer to http://www.php.net/manual/en/mysqli-stmt.bind-param.php
for other options to the first argument of the bind_param() method.
/* create a prepared statement */
if ( $stmt = $mysqli->prepare("INSERT INTO `cache` (id, idx, b64json) VALUES (?, ?, ? )") )
{
	for ($i=0, $limit=count($items); $i<$limit; ++$i)
	{
		$item=base64_encode(json_encode($items[$i]));

	    /* bind parameters for markers */
	    $stmt->bind_param("i", $id);
	    $stmt->bind_param("i", $i);
	    $stmt->bind_param("s", $item);

	    /* execute query */
    	$stmt->execute();
	}

    $stmt->close();
}

Open in new window

0
 
LVL 75

Author Comment

by:Michel Plungjan
ID: 39239371
Thanks a lot. I posted my second post without seeing yours

So PDO is better than mysqli here?

I do not need to search the json string just store it.


I will look at your code tomorrow
0
 
LVL 82

Expert Comment

by:hielo
ID: 39239946
>>So PDO is better than mysqli here?
I have never actually done any benchmarks but I have used both and have found the PDO API easier, specifically in the aspect of prepared statements.  You just provide an array of values and done (as opposed to the bind_param() method of mysqli).

Additionally, if you use PDO, if for whatever reason you must migrate your application to a different DB server (say postgres) then the only change required on my first post would be to change:
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';

to:
$dsn = 'pgsql:dbname=testdb;host=127.0.0.1';

Put another way, given the various dbs that PDO supports,  once you learn it (or become familiar/comfortable with it), if you then use it on new projects AND if any of those projects need to be moved later on onto a different DB, then there's minimal work to be done.

As far as my first post, the lines that need to be updated based on your second post are:
//line 13:
$sth = $dbh->prepare('INSERT INTO `cache` (id, idx, b64json) VALUES (?, ?, ? )');

//line 34
$item=base64_encode(json_encode($items[$i]));

//line 41:
$sth->execute( array($id, $i, $item) );

On another note, if you search mysqli vs PDO, you should be able to find a lot of opinions.  Again, for me the selling point are the portability and the simple prepared statements API.

Regards,
Hielo
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39240772
This article may be helpful with questions about MySQLi vs PDO.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Just a few philosophical notes...

If you store the query parameters and the JSON results in a way that lets you coordinate the information, you're doing the right thing.

JSON is just a transport mechanism, similar to XML.  If you were retrieving the data in an XML string, it would be susceptible of carrying the same information in key-value pairs as is JSON.

When JSON_Decode() runs you get an object that has properties, some of which may be of interest in subsequent queries.  It might make sense to decode the JSON string and save some of the properties in indexed columns to facilitate future queries.

When JSON_Decode() fails, you can get a little bit of error information.
http://php.net/manual/en/function.json-last-error.php

JSON strings are always UTF-8.
http://www.json.org/
0
 
LVL 75

Author Comment

by:Michel Plungjan
ID: 39240897
The data is returned in JSON only.

It is then saved to not have to use the api again (it is not free) just to see a previous result.

So the encode is to handle all quotes and special chars and even foreign language in the info.

Thanks for the information. I will try to put it all together
0
 
LVL 75

Author Comment

by:Michel Plungjan
ID: 39241934
Hielo here is the complete php
I do not understand where
$errors=array();
is used

<?php
include ('pdo_functions.php'); // contains $dsn = 'mysql:dbname=stJson;host=127.0.0.1'; $user = 'dbuser'; $password = 'dbpass';

function error_msg($text) { 
     $pre = '{"error:=":';  
     $post = '"}'; //example of addon to end 
     die($pre.$text.$post); 
} 

$phpObj = json_decode(someApiReturningJSON());

if( is_null($phpObj) ) {
  error_msg("Unable to decode data");
}

$items = $phpObj->items;
echo count($items)." found";

$errors=array();

try {
  $dbh = new PDO($dsn, $user, $password);
  //force PDO to throw exceptions when errors are encountered
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $dbh->beginTransaction();
  try {
    /* Execute a prepared statement by passing an array of values */
    $sth = $dbh->prepare('INSERT INTO `cache` (id, idx, b64json) VALUES (?, ?, ? )');
    for ($i=0, $limit=count($items); $i<$limit; ++$i) {
      $title = $items[$i]->title;

      $item=base64_encode(json_encode($items[$i])); // re-jsonencode and then b64
      
      //if an error were to occurr while executing the command, an
      //exception will be thrown (due to the setting specified 
      //above immediately after connecting to the db).  So you don't need
      //to check if the statement succeeded or failed.  It will be caught
      //in the "catch" clause.
      $sth->execute( array($id, $i, $item) );
    }
    //if you make it this far, no exceptions were thrown - everything was OK
    $dbh->commit();
  }
  catch(PDOException $e) {
    //if you make it here, there were problems in the prepare() OR execute() method calls.
    $dbh->rollBack();
    echo "Error!: " . $e->getMessage() . '<br />'; 
  }
}
catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}
?>

Open in new window

0
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 39242035
>>I do not understand where $errors=array();
I apologize for that.  I was originally going to append errors into that array.  The plan was to do explicit checks for the success of the  execute() method and if it failed then get error info via errorInfo() method (http://www.php.net/manual/en/pdo.errorinfo.php).  But then I remembered that I can (and that it would be cleaner) if I just use:
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I forgot to remove that line.

On another note, I don't see where you are initializing the variable $id which is used/needed (based on post ID: 39239147) on line 39. Other than that, it looks OK.

Lastly, if I understand the logic behind function error_msg(), you are going to use that to send json-encoded error messages.  If that is the case, then perhaps having the "key" be simply "error" (as opposed to "error:=" might be simpler.

Also, if you want to see these db errors on the calling api (at least while in debug/development), then you may want to change line 47 to:
error_msg( $e->getMessage() );

the same rationale with line 51.

Regards,
Hielo
0
 
LVL 75

Author Comment

by:Michel Plungjan
ID: 39242221
Ah, the := is a typo. Thanks.

The id is now a get var. all works lovely except the foreign key id was set to unique
0
 
LVL 82

Expert Comment

by:hielo
ID: 39245378
>>except the foreign key id was set to unique
Does this mean this is already solved or do you still need more help?
0
 
LVL 75

Author Closing Comment

by:Michel Plungjan
ID: 40866828
better late than nrever. The project was alas scrapped
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now