PLV8JS and PLCoffee Part 2B: PHP JQuery App   Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

Postgres OnLine Journal

Blog View    Periodical View   PostGIS in Action   About the Authors   Consulting



Friday, August 10. 2012


PLV8JS and PLCoffee Part 2B: PHP JQuery App

Printer Friendly

In our last article, PL/V8JS and PL/Coffee JSON search requests we demonstrated how to create a PostgreSQL PL/Javascript stored function that takes as input, a json wrapped search request. We generated the search request using PostgreSQL. As mentioned, in practice, the json search request would be generated by a client side javascript API such as JQuery. This time we'll put our stored function to use in a real web app built using PHP and JQuery. The PHP part is fairly minimalistic just involving a call to the database and return a single row back. Normally we use a database abstraction layer such as ADODB or PearDB, but this is so simple that we are just going to use the raw PHP PostgreSQL connection library directly. This example requires PHP 5.1+ since it uses the pg_query_param function introduced in PHP 5.1. Most of the work is happening in the JQuery client side tier and the database part we already saw. That said the PHP part is fairly trivial to swap out with something like ASP.NET and most other web server side languages.

The PHP script

The PHP script is shown below. To keep it short, we just put the connection string right in the php script and did away with all the try / catch logic we usually put in.

<?php
$db = pg_pconnect("host=localhost port=5432 dbname=testplv8 user=demouser password=foofoo");

if (isset($_POST['json_search'])){
    /** parameterized sql statement that takes a json object as input **/
    $sql = 'SELECT simple_search_inventory($1::json);';
    /** Execute the statement passing the json object in Post **/
    $result = pg_query_params($db, $sql, array($_POST['json_search']) );
    $row = pg_fetch_row($result);
    pg_free_result($result);
    pg_close($db);
     /**output json result **/
    echo $row[0];
}
else {
    echo "no request made"; 
}
pg_close($db);?>

JQuery Client-side Code

For the client side JQuery side, we did away with all that css fanciness to make this short. Also not much in the way of error handlers. The searchInventory function that gets triggered on click of the search button does most of the work by packaging the form search variables into a json request, handing it off to the php script, and then processing the json dataset that comes back. A good portion of the code uses JQuery idioms and functions.

<html>                                                                  
 <head><title>Inventory Search App</title>                                                               
 <script type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
 <script type="text/javascript"> 
 /** this is boiler plate code from: 
  http://jsfiddle.net/sxGtM/3/ converts array of form elements to associative array **/
 $.fn.serializeObject = function()
{
    var o = {};
    var a = this.serializeArray();
    $.each(a, function() {
        if (o[this.name] !== undefined) {
            if (!o[this.name].push) {
                o[this.name] = [o[this.name]];
            }
            o[this.name].push(this.value || '');
        } else {
            o[this.name] = this.value || '';
        }
    });
    return o;
};

function searchInventory() {
    frmdata = $('#frmSearch').serializeObject();
    /** remove all rows after header and template row
     to prevent appending of results from prior searches **/
    $('#tblresults').find("tr:gt(1)").remove();
    /** post JSON formatted search input and get back JSON dataset **/
    $.post('inventory_app.php', {'json_search': JSON.stringify(frmdata, null, '\t')}
       ,function(data) {
            var rs = $.parseJSON(data);
            $('#spandetail').html('There are ' + rs.length + ' item(s).')
        /** Look through rows of dataset **/
            $.each(rs, function() {
                var result = $('#template_row').html();
                /** Look through columns of row **/
                $.each(this, function(cname,val) {
                    /** hack to html encode value **/
                    htmlval = $('<div />').text(val).html();
                    /** replace :column_name: in template with column value **/
                    result = result.replace(':' + cname + ':', htmlval);    
                });
                $("#tblresults").append('<tr>' + result + '</tr>');
            });
            /** hide our template row **/
            $('#template_row').hide();
            /**show our search results **/
            $('#tblresults').show();
       }
    ); 
}
 </script>                                                               
 </head>                                                                 
 <body>                                                                  
   <form id="frmSearch">
        <b>Product Name:</b> <input type="text" id="prod_name_search" name="prod_name_search" style="width:100px" />
        <b># per page</b> <input type="text" name="num_per_page" value="100" style='width:30px' readonly /> 
        Page: <input type="text" name="page_num" value="1" style='width:30px' readonly />
        <input type="button" name="cmdSearch" value="search" onclick="searchInventory()" />
   </form>
   <span id='spandetail'></span>
   <table id="tblresults" style='display:none'>
        <tr><th>Code</th><th>Product Name</th></tr>
        <tr id='template_row'><td>:prod_code:</td><td>:prod_name:</td></tr>
   </table>
 </body>                                                                 
</html>

The output screen looks like this:

When you do a search and click the search button you get this:

Posted by Leo Hsu and Regina Obe in application development, intermediate at 13:32 | Comments (0) | Trackbacks (0)

Trackbacks
Trackback specific URI for this entry

PingBack
Weblog: www.postgresonline.com
Tracked: Sep 23, 21:42


Comments
Display comments as (Linear | Threaded)

No comments

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
 

Entry's Links

Quicksearch

Calendar

Back November '24
Mon Tue Wed Thu Fri Sat Sun
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

Categories

All categories

Archives

Subscribe

  • XML

Blog Administration

Open login screen