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:
