<?php
//==================================================================================
// JSONP: A PHP process that returns JSONP
//        JSONP is JSON that is wrapped in a callback function
//        format:  function( JSON );
//  
//        Receives: sql:      The sql code that you want to execute
//                  callback: function to call back  
//        Returns:  data from database as JSONP output
//                  if callback function is requested, then JSONP, otherwise JSON
//==================================================================================
error_reporting(E_ALL ^ E_WARNING ^ E_NOTICE);  //all except warnings & notices

    $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 $sql, $callback;

    $sql       = $_GET['sql'];
    $callback  = $_GET['callback'];

    $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 $sql, $data, $error;

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

    $database  = 'demo';                         #get HTML form param fields
    $userid    = 'demo';
    $password  = 'demo';
    $host      = 'localhost';
    $port      = '';

    if (! $sql)
    {
       $error='Enter URL?sql=...&callback=...';
       return;
    }

    $connect = mysqli_connect($host,$userid,$password,$database);
    if (! $connect)
    {
        $error = 'Could not connect: ' . mysqli_connect_error();
        return;
    }

    if ( preg_match('/^\s*(select|desc|show)/i', $sql) )        #only allow select, desc or show    
    {
        $result = mysqli_query($connect, $sql);                 #execute the query
        if (! $result)
        {
            $error = 'Could not execute query: ' . mysqli_error($connect);
            return;
        }

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

        $fields = mysqli_fetch_fields($result);                 #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_assoc($result);            #store row in 2 dim data array

        mysqli_free_result($result);                            #free result buffer 
    }
    else                                                        #if not a cursor (e.g. result of an update) 
    {
        $error = "Update Not Allowed";    
    }
    mysqli_close($connect);
}

//=============================================================================
function send_result()
{
    global $sql, $data, $error, $callback;

    //------if no sql requested---------------------------------------

    if (! $sql)                                      #if no sql requested 
    {
        header("Content-type: text/html");
        
        if ($error)
            print "<h3> $error </h3> \n";

        include "include.php";
        return;
     }

    //------if sql requested---------------------------------------

    header("Content-type: text/plain");
       
//  $jsonString = json_encode($data);                           #convert php array of object to JSON format

    $jsonString =  "[ \n";

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

        foreach ($row as $colName => $colValue )                #loop thru column names/values
        {
            $colName     = str_replace(' ','_', $colName);      #if colName alias has ' ', change to '_' 
            $colValue = ($colValue) ? $colValue : ' ';     #if colValue is null,  change to ' ' 
            $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 .=  "]";

    if (isset($callback)) $openParen  = '(';
    if (isset($callback)) $closeParen = ')';

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

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