PHP Classes

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

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

Author

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: 33
All time rank: 3240124 in Italy Italy
Week rank: 416 Up16 in Italy Italy Up

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  
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

 Version Control Unique User Downloads Download Rankings  
 0%
Total:113
This week:1
All time:9,580
This week:560Up