PHP Classes

PHP DB Query to JSON: Run SQL in multiple database and get JSON results

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 113 All time: 9,605 This week: 206Up
Version License PHP version Categories
db_connector 1.0.1Custom (specified...7PHP 5, Databases
Description 

Author

Simone Gosetto

This package can connect to different databases and execute queries.

It provides a base class that has functions to connect to a database server, execute SQL queries and return query result values.

The sub-classes need to extend the base class so they can implement the support to different SQL database servers like MySQL, Microsoft SQL Server, PostgreSQL and SQLite.

The base class can also compose queries based on joins of tables and fields passed as parameters, as well return the results of a query as a string in JSON format.

Picture of Simone Gosetto
  Performance   Level  
Name: Simone Gosetto <contact>
Classes: 2 packages by
Country: Italy Italy
Age: 34
All time rank: 3241124 in Italy Italy
Week rank: 195 Up8 in Italy Italy Up

Level 2 StarStar

Example

<?php

include "SG_DB.php";

// For hide notice
error_reporting(E_ERROR | E_WARNING | E_PARSE);

#MICROSOFT SQL SERVER
/*
include "SG_MsSql.php";

$ms = new SG_MsSql();
if($ms->connected)
{
    //$result = $ms->exportJSON("SELECT * FROM table");
    //$result = $ms->countRows("SELECT * FROM table");
    $result = $ms->executeSQL("INSERT INTO table values(your_value)");

    if(strlen($ms->lastError) > 0)
    {
        echo $ms->lastError;
        $ms->closeConnection();
    }
    else
    {
        echo $result;
        $ms->closeConnection();
    }

}
else
{
    echo $ms->lastError;
}
*/


#SQLITE
/*
include "SG_SQLite.php";

$lite = new SG_SQLite("SQLite_db_test.db");

if($lite->connected)
{
    //$result = $lite->executeSQL("CREATE TABLE test1(id int, desc varchar(10))");
    //$result = $lite->executeSQL("INSERT INTO test1(id,desc) VALUES(3,'test 3')");
    $result = $lite->exportJSON("SELECT * FROM test1");
    //$result = $lite->countRows("select * from test1");


    if(strlen($lite->lastError) > 0)
    {
        echo $lite->lastError;
        $lite->closeConnection();
    }
    else
    {
        echo $result;
        $lite->closeConnection();
    }
}
else
{
    echo $lite->lastError;
}
*/

#MYSQL
/*
include "SG_Mysql.php";

$my = new SG_Mysql();

if($my->connected)
{
    //$result = $my->executeSQL("CREATE TABLE test1(id int, description varchar(10));");
    //$result = $my->executeSQL("INSERT INTO test1(id,description) VALUES(3,'test 3');");
    $result = $my->exportJSON("SELECT * FROM test1");

    if(strlen($my->lastError) > 0)
    {
        echo $my->lastError;
        $my->closeConnection();
    }
    else
    {
        echo $result;
        $my->closeConnection();
    }
}
else
{
    echo $my->lastError;
}
*/

#POSTGRESSQL
/*
include "SG_PostgreSQL.php";

$pg = new SG_PostgreSQL();

if($pg->connected)
{
    //$result = $pg->executeSQL("CREATE TABLE test1(id int, description varchar(10));");
    //$result = $pg->executeSQL("INSERT INTO test1(id,description) VALUES(3,'test 3');");
    //$result = $pg->exportJSON("SELECT * FROM test1");
    $result = $pg->countRows("SELECT * FROM test1");

    if(strlen($pg->lastError) > 0)
    {
        echo $pg->lastError;
        $pg->closeConnection();
    }
    else
    {
        echo $result;
        $pg->closeConnection();
    }
}
else
{
    echo $pg->lastError;
}
*/

#TEST CROSS JOIN

include "SG_SQLite.php";
include "SG_MsSql.php";

$lite = new SG_SQLite("SQLite_db_test.db");
$ms = new SG_MsSql();

if($lite->connected && $ms->connected)
{
    //prepare parent
    $lite->prepareForCrossJoin(
        'select * from test1', //query master
        'id', // master field name for join
        null
    );

    //prepare child
    $ms->prepareForCrossJoin(
        'select * from stato', //query child
        'Stato', // child field name for join
        'Descr' // child field name to exstract (description) ONLY USED IN CHILD OBJECT (if '*' take all fields of RecordSet)
    );

    $resultJoin = $lite->executeCrossQuery($ms);

    if(strlen($lite->lastError) > 0 || strlen($ms->lastError) > 0)
    {
        echo $lite->lastError." ".$ms->lastError;
        $lite->closeConnection();
        $ms->closeConnection();
    }
    else
    {
        //echo $resultJoin;
        var_dump($resultJoin);
        $lite->closeConnection();
        $ms->closeConnection();
    }
}
else
{
    echo $lite->lastError." ".$ms->lastError;
}


Details

I've thought to realize a set of classes ?connector? for the varius DB, starting from a one abstract class, in order that the classes have almost the same functions.

The additional functional character consists in doing a JOIN between query of different connectors
(the example is on the relevant page), so that you can have a complete array on output with the merge of that one we have outlined on the configuration of the CROSS QUERY.

Configuration:

Into the file ?config.inc.ini? you have to configurate the credentials of the varius DB, in which you want to enter on. The credentials will be used by the relatives classes.

I HAVE DONE ALL MY TESTS WITH PC WWINDOWS 7/10 WITH XAMPP AND PHP 7.2.3

###################### SQL SERVER (MSSQL) ######################

Extensions: 
    "extension=sqlsrv_72_ts_x86"
    "extension=sqlsrv_72_ts_x64"

Useful Links for the download and the configurarion of the library srvsql for PHP 7:
 - https://www.microsoft.com/en-us/download/confirmation.aspx?id=56567 
 - https://www.microsoft.com/en-us/download/confirmation.aspx?id=56729
 
############################ SQLITE ############################

Extension: "extension=php_sqlite3.dll"
  
Files utili:
 - SQLite_db_test.db -> esempio di db
  
############################# MYSQL #############################

MySql is based on the library "mysqli" standard for PHP 5+

########################## POSTGRESSQL ##########################

Extension: "extension=php_pgsql.dll"

Tested with PostgresSQL 10

#################################################################

EXAMPLES:
  Into the file ?example.php? you find all the examples of code of the varius DB

  Files folder image Files (10)  
File Role Description
Accessible without login Plain text file config.inc.ini Conf. config
Accessible without login Plain text file example.php Example example script
Accessible without login Plain text file LICENSE Lic. license
Accessible without login Plain text file readme_EN.md Doc. en documentation
Accessible without login Plain text file readme_IT.md Doc. it documentation
Plain text file SG_DB.php Class db master class
Plain text file SG_MsSQL.php Class sql server class
Plain text file SG_Mysql.php Class mysql class
Plain text file SG_PostgreSQL.php Class postgresql class
Plain text file SG_SQLite.php Class sqlite class

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloaddb_connector-2018-04-20.zip 9KB
Downloaddb_connector-2018-04-20.tar.gz 6KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 0%
Total:113
This week:0
All time:9,605
This week:206Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.