Save JSON data in mySQL database

<?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();
	}
}
?>

Select allOpen in new window

/* 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();
}

Select allOpen in new window

<?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();
}
?>

Select allOpen in new window