<?php
//=============================================================================
// Web Service
//       Receives name:    The student name (or ALL for all students)
//                format:  The type of output format desired XML or JSON (default)
//       Returns: data from database as either JSON format (default) or XML format
//                if XML  then XML standard
//                if JSON then array of objects as:
//                        [ 
//                            {"name":"value", "name2":"value2", ...}, 
//                            {"name":"value", "name2":"value2", ...} 
//                        ]  
//=============================================================================

    error_reporting(0);          #turn off PHP error reporting

    $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 $name, $format, $error;

    $name   = $_GET['name'];                #get HTML form param fields
    $format = $_GET['format']; 	 
    
    if (! $name)
    {
       $error='Enter URL?name=...|ALL&format=XML|JSON';
       include "../include.php";
       return;
    }
    if (! $format) $format = 'JSON';		        #if not provided use JSON     
}

//=============================================================================
function execute_sql()
{
    global $name, $format, $data, $error;

    $connect = mysqli_connect("p:localhost","demo","demo","demo");   #persistent connection
    if (! $connect)
    {
        $error = 'Could not connect: ' . mysqli_connect_error();
        return;
    }

    if ($name=='ALL' || $name=='all')
        $name = '%';        

    $sql = "SELECT student_id,lname,fname,ssn,sex,course_id,description,price  
            FROM student left join class on ssn=stu_ssn                      
                         left join course using(course_id)                     
            WHERE lname LIKE '$name%'";       
//print $sql; 

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

    $i=0;
    while ($row = mysqli_fetch_assoc($cursor))    #loop thru rows
        $data[$i++] = $row;                       #store row (col=>val) in 2 dim data array

    mysqli_free_result($cursor);                  #free result buffer

}

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

    if ($error)
    {
        print "<h3> $error </h3> \n";
        include "../include.php";
        exit;
    }

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

    if ($format=='XML' || $format=='xml')               #if requesting XML 
    {
        header("Access-Control-Allow-Origin: *");		#allow access across domains
        header("Content-type: text/xml");               #sending XML
        
        print "<?xml version='1.0'?> \n";

        print "<sqlData> \n";

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

            foreach($row as $colName => $colValue)          #loop thru each row (column=value)						
                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("Access-Control-Allow-Origin: *");		    #allow access across domains
        header("Content-type: text/plain");                 #sending plain text
                
        $jsonString =  "[ \n";

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

            foreach($row as $colName => $colValue)          #loop thru each row (colum=value)
                $jsonString .= "\"$colName\":\"$colValue\", ";

            $jsonString = preg_replace("/, $/", "}, \n", $jsonString);   #change the last comma to '},\n'
        }
        $jsonString = preg_replace("/, \n$/", " \n", $jsonString);       #strip off the last comma for entire json

        $jsonString .=  "] \n";

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

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