Click here to Skip to main content
15,868,016 members
Articles / Database Development / MariaDB
Tip/Trick

Create Table SQLite and MariaDB (Mysql) from JSON Object using PHP PDO.

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
31 Mar 2023CPOL4 min read 6.4K   70   2  
A PHP class that uses PDO for creating a table from JSON Objects
This tip is about a PHP class which uses PDO for creating a table (SQLite and MariaDB tried) from JSON objects.

Try it yourself.

Introduction

In an application, there is often a need to store information which, despite being classifiable under a common typology, nonetheless contain different data, for example, a table of events that contains the information like logging, particular activities on data and so on; a minimal solution can be a table with three fields:

  • event type
  • JSON particular data
  • timestamp (if not in the above field)

However, with this solution, the problem arises of recovering such data perhaps in a form compatible with relational tools id is a table, hence this work, in fact, illustrates a possible solution to this problem using the PHP Data Objects (PDO) extension.

Background

The JSON functions and operators were introduced in the SQLite release 3.37.2 and are built by default from the version 3.38.0 (2022-02-22)1.

The PHP script described in this document does not use the features offered by SQLite and can therefore be used with versions of SQLite lower than those mentioned above.

This is an update of the product to overcome its limitations, in particular in the previous version the JSON objects had to have the same structure this, incidentally, hid, without consequences, a conceptual error.

The software has been tried with Data  base SQLite and MariaDB (Mysql) .

Using the Code

The script json2table.php contains the class of the name J2t; the class contains three public functions:

  • json2table
  • execSQL
  • extractFields

The first two functions assume that the database has already been opened (i.e., by creation of a new PDO object).

The scope of the first one is to create a temporary table using an array of JSON Objects or an SQL statement, the second is also used internally.

Generation of the Temporary Table

The json2table function structure is:

PHP
json2table($dbh,$data,$tableName="TemporaryTable",$flds=array())

where:

  • $dbh is an instances of the PDO base class
  • $data is an array of JSON objects or an SQL SELECT statement
  • $tableName is the name of the temporary table created
  • $flds is an optional array of field names to extract from input for the table to be created.

The input data

If the second parameter of the json2table function is a SQL SELECT command, the fields involved can be JSON strings and possibly normal fields, in particular, the JSON field(s) can be an object, i.e., a set of name/value pairs or an array of dimension 1 containing an object.

Object {"Author":"Alan Perlis","Source":"","Quote":"Syntactic sugar causes cancer of the semicolon."}
Object in array [{"_id":"9sgo4-hjyyq","author":"The Buddha","content":"Radiate boundless love towards the entire world — above, below, and across — unhindered, without ill will, without enmity.","tags":["Wisdom","Love"],"length":122,"dateAdded":"2023-03-30"}]

An item can be an array, for example, the above "tags":["Wisdom","Love"] becomes a table field with name tags and value Wisdom, Love.

The table structure

The fields of the generated table will be those of the first element of the input data set or those indicated in the possible fourth parameter of the method, this parameter is an array of field names and possibly data type.

PHP
include 'json2table.php';
$DBname = "json.sqlite";
$db = new PDO("sqlite:$DBname");
$j2table = new J2t;
...
$sql = "SELECT * FROM Advanced";
$ans = $j2table→json2table($db,$sql,$tTable,array("Store","Qty","Box"=>"TEXT");

The field data type is inferred from the input data unless indicated in the fourth parameter; the value accepted are TEXT, INTEGER and REAL.

The usefulness of the fourth parameter is in being able:

  • to obtain a subset of the data present,
  • to possibly indicate the type of data, for example, treating data stored as character as numeric,
  • to indicate field(s) not present in the first row.

The output data

The function returns:

  • an empty string if the table was created,
  • No Data! If the JSON array is empty or the SELECT statement returns an empty set.

It is also created a temporary table for possibly errors.

Example 1: Use With Array of Data and Call the Class Function

PHP
...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);    
$table = "tTable";
J2t::json2table($dbh,$result,$table);
...

Execute SQL Commands

The function is used by the json2table function but is also a method and can be used for execute SQL commands:

PHP
execSQL($dbh,$sql,$parms=[])

The optional $parms is an array of values with the elements that are bound in the SQL statement being executed, see the Example 2 below.

The function returns a PDOStatement object or an error if the SQL isn’t correct, in order to obtain the error, the function sets the error mode to SILENT; the previous mode is restored after the command execution.

ExecSQL Function

PHP
public static function execSQL($dbh,$sql,$parms=[]) {
    $errMode = $dbh→getAttribute(PDO::ATTR_ERRMODE); // save previous ATTR_ERRMODE
    $dbh->setattribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);    // disable crash
    $sth = $dbh->prepare($sql);
    $a = $dbh->errorInfo();
    $dbh->setattribute(PDO::ATTR_ERRMODE,$errMode);  // restore previous ATTR_ERRMODE
    if (!$sth) {return "\n<br>$sql\n<br>".$a[2];}
    $sth->execute($parms);
    return $sth;
}

Example 2: Use with SQL and Call of Class Method

PHP
...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs'";
$y2t = new J2t;
$answer = $y2t->json2table($dbh,$sql,"t");
if ($answer != "") exit($answer);		// Something went wrong
$result = $y2t->execSql($dbh,"SELECT Count(*) Count, SUM(Qty) Qty FROM t");
if (gettype($result) == "string") echo $result;  // SQL problem
else print_r($result→fetchAll(PDO::FETCH_ASSOC));
$sql = "SELECT * FROM t WHERE Qty > ?";
print_r($y2t->execSql($dbh,,Array(100))->fetchAll(PDO::FETCH_ASSOC));
...

History

  • 31st March, 2023
    • Initial version.
  • 11th May, 2023
    • Input data from SQL SELECT can contain normal fields and one or more JSON field(s),
    • an item of JSON field can be an array.
  • 18th March, 2024
    • Choice on fields,
    • enhanced type management,
    • enhanced error management.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Condor Informatique
Italy Italy
Computer literacy (software) : Languages: PHP, Javascript, SQL Autoit,Basic4Android; Frameworks: JOOMLA!
Teaching/Training skills on Office, WEB site development and programming languages.
Others : WEB site development.
UNDP Missions
feb – may 2003 Congo DR Bukavu: ground IT computer course
nov 2003 Burundi Bujumbura: Oracle Data Base course
feb 2005 Burundi Bujumbura: JAVA course
mar 2005 Mali Kati: MS Office course
oct 2006 Mali Kati: MS Office course
jun 2006 Burkina Faso Bobo Dioulasso: MS Office course
jun 2007 Burkina Faso Bobo Dioulasso: MS Office course
may 2007 Argentina Olavarria hospital: Internet application for access to medical records
apr 2008 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
jun 2008 Niger Niamey: analysis of the computing needs of the Niamey hospital
may 2009 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
oct 2010 Niger Niamey: analysis of the computing needs of the Niamey hospital (following)
Region Piedmont project Evaluation
mar 2006 Burkina Faso, Niger
mar 2007 Benin, Burkina Faso, Niger
sep 2008 Benin, Burkina Faso, Niger
Others
feb 2010 Burundi Kiremba hospital: MS Office course
feb 2011 Congo DR Kampene hospital: MS Office course

Comments and Discussions

 
-- There are no messages in this forum --