<?php
//=============================================================================
// Web Service to access a Mysql database
//       Receives user:   The database user id
//                pswd:   The database password  
//                db:     The database name (Mysql database name)  
//                sql:    The sql code that you want to execute   
//                format: The output format XML or JSON, if none = JSON
//                host:   (optional) The hostname,       if none = localhost  
//                port:   (optional) The port number,    if none = 3306  
//       Returns: data from database in either XML format or JSON format
//                if XML  then standard XML
//                if JSON then array of objects as:
//                        [ 
//                            {"name":"value", "name2":"value2", ...}, 
//                            {"name":"value", "name2":"value2", ...} 
//                        ]  
//=============================================================================

error_reporting(E_ALL ^ E_WARNING ^ E_NOTICE);  //all except warnings & notices

    $colNames = array();         #array to hold returned column names
    $data     = array();         #2 dim array to hold returned data

    read_param();                #read data from the query String
    execute_sql();               #execute the SQL                        
    send_result();               #send the results using either XML or JSON

//=============================================================================
function read_param()
{
    global $database, $userid, $password, $sql, $host, $port, $format;

    $database  = $_GET['db'  ];                 #get HTML form param fields
    $userid    = $_GET['user'];
    $password  = $_GET['pswd'];
    $sql       = $_GET['sql' ];
    $format    = $_GET['format'];         
    $host      = $_GET['host'];
    $port      = $_GET['port'];

    if (! $format) $format = 'JSON';            #if no format, use JSON

//$database="demo2";                            #hardcoded for debug
//$userid="demo2";
//$password="demo2";
//$sql="show tables";
//$format="json";
//$host="localhost";
//$port="";

    $sql = str_replace('\\', '', $sql);       #php adds \ before any quotes
                                              #need to preserve those for mysql
    $sql = str_replace('~', '%', $sql);       #I used ~ instead of %
                                              #% is used for url encoding
}

//=============================================================================
function execute_sql()
{
    global $connect, $database, $userid, $password, $sql, $host, $port, $format;
    global $error;

//    error_reporting(0);                          #turn off PHP error reporting

    if (! $userid or ! $password or ! $database or ! $sql)
    {
       $error='Enter URL?user=...&pswd=...&db=...&sql=...&host=...&format=XML|JSON';
       return;
    }

    if (strpos(strtolower($database),'demo') != false && strpos(strtolower($sql),'select') === false)     //if demo DB, only allow select
    {
       $error='Access to "demo*" databases is in read mode only';
       return;
    }

    $host2 = ($host) ?     $host : "localhost";  #if host use it else localhost
    $port2 = ($port) ? ':'.$port : "";           #if port append ':' to it

    $host_port = $host2 . $port2;

    $connect = mysqli_connect($host_port,$userid,$password,$database);   
    if (! $connect)
    {
        $error = 'Could not connect: ' . mysqli_connect_error();
        return;
    }
                                       #if $sql start with (not case sensitive)
    if ( preg_match('/^\s*(select|desc|show|explain)/i', $sql) )    
        db_read();                             #select, desc, show or explain  
    else                                       #then call db_read( )
        db_write();                            #else call db_write( )

}

//=============================================================================
function db_read()
{
    global $connect, $sql, $colNames, $data, $msg, $error;

    $cursor = mysqli_query($connect, $sql);                            #execute mysql query 
    if (! $cursor)
    {
        $error = 'Could not execute query: ' . mysqli_error($connect);
        return;
    }

    $numCols = mysqli_num_fields($cursor);        #number of columns returned
    $numRows = mysqli_num_rows($cursor);          #number of rows returned

    $fields = mysqli_fetch_fields($cursor);       #get all fields as an array of objects
    for ($i=0; $i < $numCols; $i++)               #loop thru the array
        $colNames[$i] = $fields[$i]->name;        #store the field name in col array


    for ($i=0; $i < $numRows; $i++)               #loop thru rows
        $data[$i] = mysqli_fetch_row($cursor);    #store row in 2 dim data array

    mysqli_free_result($cursor);                  #free result buffer

    $msg = "$numRows rows returned";
}

//=============================================================================
function db_write()
{
    global $database, $connect, $sql, $msg, $error;

    if (preg_match('/demo/i',$database))                    #if database user is demo*
    {
        $error = 'Insert/update not allowed for demo* database';
        return;
    }

    $result = mysqli_query($connect, $sql);                 #execute the update

    if (! $result)
    {
        $error = 'Could not execute sql: ' . mysqli_error($connect);
        return;
    }

    $numRows = mysqli_affected_rows($connect);            #number of rows affected

    $msg = "$numRows row(s) affected";
}

//=============================================================================
function send_result()
{
    global $colNames, $data, $msg, $error, $format;

    //------if error---------------------------------------

    if ($error)                                         #if error 
    {
        header("Content-type: text/html");        

        print "<h3> $error </h3> \n";
        include "zCode/include.php";                #to display the php code
        return;
    }

     //------for XML-----------------------------------------------------------

    if ($format=='XML' || $format=='xml')               #if requesting XML 
    {
        header("Content-type: text/xml");
        
        print "<?xml version='1.0'?> \n";

        print "<sqlData> \n";

        foreach($data as $row)                          #loop thru the rows
        {
            print "    <row> \n";

            for ($i=0; $i<count($row); $i++)            #loop thru column names
            {
                $colName  = $colNames[$i];
                $colName  = str_replace(' ','_', $colName);      #if column alias has ' ' 
                $colValue = $row[$i];
                print "        <$colName>$colValue</$colName> \n";
            }
            print "    </row> \n";
        }

        if ($error)
            print "<error> $error </error> \n";

        print "</sqlData> \n";
     }

    //-----For JSON-----------------------------------------------------------

    if ($format=='JSON' || $format=='json')             #if requesting JSON 
    {
        header("Content-type: text/plain");
        
        $jsonString =  "[ \n";

        foreach($data as $row)                          #loop thru the rows
        {
            $jsonString .= "    {";

            for ($i=0; $i<count($row); $i++)            #loop thru column names
            {
                $colName  = $colNames[$i];
                $colName  = str_replace(' ','_', $colName);         #if column alias has ' ' 
                $colValue = $row[$i];
                $jsonString .=  "\"$colName\":\"$colValue\", ";
            }
            $jsonString = preg_replace("/, $/"," ", $jsonString);   #strip off the last comma for the object

            $jsonString .= "}, \n";
        }
        $jsonString = preg_replace("/, \n$/"," \n", $jsonString);   #strip off the last comma for the array 

        $jsonString .=  "] \n";

        if ($error)
            print "[ {\"error\":\"$error\"} ] \n";
        else 
            print $jsonString;
     }}

//=============================================================================
?>