REST in PostgreSQL Part 2 B - The REST Server service with PHP 5

This is a continuation of our REST series. The following topics have already been covered

1. Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't
2. REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search function that spits out XML to support our rest server service
3. REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET we demonstrated a REST web service using Mono.NET, MS.NET both in C#, VB.Net/Monobasic

Now in this Part 2B series, we shall demonstrate the same REST server service using PHP

Setting up the PHP application

1. PHP already has the PostgreSQL drivers available as a .so (on Linux) or .dll on Windows. For windows users if you are running PHP under IIS and in ISAPI mode, you will not be able to dynamically load libraries, so you need to enable php_pgsql in your PHP.ini file.

2. We tend to keep it enabled regardless of which platform we are on since a lot of our PHP development involves PostgreSQL. The extension is php_pgsql in the php.ini file
3. PHP has numerous database abstraction libraries to choose from. We are using the adodb abstraction library for PHP which can be downloaded from http://adodb.sourceforge.net/.

First we have a simple php config file that looks as follows.

--Contents of config.inc.php

<?php
    $CONNSTRING = "postgres://pagila_app:pg@123@localhost:5432/pagila?persist";
?>

And now for our php rest service. Keep in mind this is only one of millions of ways of doing this in PHP. The nice thing about PHP is that it gives you lots of freedom. The bad thing about PHP is that it gives you lots of freedom.

/**pagilasearch_php.php **/

<?php
include_once("config.inc.php");
include_once("adodb/adodb.inc.php");
include('adodb/adodb-exceptions.inc.php');
class PagilaSearch_PHP {
    function __construct() {
        $this->process_request();
    }
    
    function __destruct() { //do cleanup
        
    }
    
    protected function process_request() {
        $maxrecs = 30; $offset = 0;
        if (!empty($_REQUEST["query"])){
            header('Content-type: text/xml');
            if (is_numeric($_REQUEST["maxrecs"])) {
               $maxrecs = $_REQUEST["maxrecs"];
            }
         
            if(is_numeric($_REQUEST["offset"])) {
               $offset = $_REQUEST["offset"];     
            }
            echo $this->get_results($_REQUEST["query"], $maxrecs, $offset);
        }
    }
    
    protected function get_results($aquery, $maxrecs, $offset){
        global $CONNSTRING;
        $conn =  &ADONewConnection($CONNSTRING);
        
        if (!$conn->PConnect()) {
            $result = "<error>ERROR Can not connect to db</error>";
        } 
        else {
            $conn->SetFetchMode(ADODB_FETCH_ASSOC);
            $conn->debug = false;
        }
        try {$rs = $conn->Execute("SELECT fnget_film_search_results(" . $conn->qstr($aquery) . ", " . 
                    (int) $maxrecs . ", " . (int) $offset . ") As answer")->GetRows();
            
            $result = $rs[0]['answer'];
        }
        catch(Exception $e){
             $result = "<error>ERROR " . $e . "</error>";
        }

        return $result;
    }   
}

new PagilaSearch_PHP();
?>





Testing our REST Service
Now to test our service - we shall try searching for films not about dentists and not about dogs but involving mad nigeria
Our search phrase is not dentist not dog mad nigeria and our browser call is like 
http://localhost/pagilasearch_php.php?query=not%20dentist%20mad%20not%20dog%20nigeria&maxrecs=20&offset=1 



The result of our REST query looks like this
<results>
 <resultsummary>
    <count>4</count> 
</resultsummary>
<table xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance" xmlns="vwfilms">
 <row>
<fid>883</fid> 
<title>TEQUILA PAST</title> 
<description>A ActionPacked Panorama of a Mad Scientist 
And a Robot who must Challenge a Student in Nigeria</description> 
<category>Children</category> 
<price>4.99</price> 
<length>53</length> 
<rating>PG</rating> 
</row>
 <row>
<fid>614</fid> 
<title>NAME DETECTIVE</title> 
<description>A Touching Saga of a Sumo Wrestler 
And a Cat who must Pursue a Mad Scientist in Nigeria</description> 
<category>Games</category> 
<price>4.99</price> 
<length>178</length> 
<rating>PG13</rating> 
</row>
 <row>
<fid>34</fid> 
<title>ARABIA DOGMA</title> 
<description>A Touching Epistle of a Madman 
And a Mad Cow who must Defeat a Student in Nigeria</description> 
<category>Horror</category> 
<price>0.99</price> 
<length>62</length> 
<rating>NC17</rating> 
</row>
</table>
</results>