A PHP back end to YUI
This is my package of PHP functions for the server side.
I have my own personal standard on how to carry communications back and forth in between client and server. All transactions follow this standard even when they might use very little of it, but it helps make things very predictable, which helps mantainability.
From the client to the server, all requests will go in a POST message in
URL-encoded format. There will always be at least two arguments, ajaxObj and
ajaxAction. The first is associated with a certain object, for
example, a DataTable or a form. The second, ajaxAction, indicates what is
it that I am requesting about that object. The rest of the arguments
depend on the particular operation.
On the reply from the server to the client, the format is as follows
{
replyCode:200, replyText:"Ok",
data:[{ ... } , ... ]
, ....
}
The replyCode and replyText are mandatory and will always be
there. The first, replyCode I set to 200 for
Ok
(much like in HTTP), 201 for Data Follows or 500 and
above for
errors. Though I adopted the numerical conventions of HTTP do not confuse
them with those. These are application error codes, not communication
codes. The browsers and caching proxies might take action on the HTTP
codes and though I could use the header function to create any HTTP reply
code I
wanted, some of them might cause unexpected behavior, very difficult to
trace. replyText hopefully will contain an 'Ok' for no error
or an appropriate message.
This message format is usually called an envelope since the data goes
inside or, as in this case, right afterward. An arbitrary number of named
properties may follow the envelope, in particular, if present, an object named data
carries tabular information. Its value will be an array of object literals, each element of
the array will be one record represented as an object literal which will have a
property named after each column containing the field value. Further properties
and their values may follow. Certain transactions expect as
a reply either an Ok or an error reply; those will carry no data.
After including the standard includes, all my AJAX server pages call ajaxReq
(shown
later)
which branches to a function
with the name pattern (in PHP syntax) "ajax_${ajaxObj}_${ajaxAction}" where $ajaxObj
and $ajaxAction are PHP variable names representing arguments coming in
the POST request.
After the call to ajaxReq there will be a series of functions
responding to that name pattern. To allow the same page to both serve AJAX and
normal page requests, ajaxReq will first look for the ajaxObj
argument; if it doesn't find one, it returns, assuming that the request
was a normal page request, not an AJAX one. If the page does only serve
AJAX request, it is enough to place an appropriate error reply right after the
call to ajaxReq. If the ajaxObj argument is there it will
search for an ajaxAction argument as well
and it should find a function named as indicated or it will give an error.
When ajaxReq reads the basic arguments and finds the corresponding function, it calls it and upon return sends back an
200 'Ok' message back with no data, this is the hopeful default. At any time the called function can return with data or an
error message. Returning some sort of data is actually the most frequent
case. If so, the processing will be terminated and the default 200
'Ok' message will never be reached.
The next important function is ajaxReply, which
takes a variable number of arguments. The first is what will become the replyCode
and it defaults to 200 if none is given, the second
is the replyText which defaults to 'Ok'. The third and remaining optional arguments can be one of two things, either an array or an
string, anything else will produce an error. Any number of them can be given in
any order.
If the extra argument is an array, it should be in the name => value format (not just
unnamed values) so that they will be
turned into named properties in the JSON reply. Care should be taken not to
repeat property names when there is more than one array and that none of those
coincide with any of the names already in use by the function. Though the
argument itself, if it is an array, has to have named elements, the value of any
of those named elements might be a simple array with no names.
If the extra argument is a string, it will assume it is an
SQL query, which will be executed and the data returned in the data
property of the reply. If there are more than one SQL query in the arguments,
the first will be in the data array, the second in a data1 array,
the third in a data2 and so on. I usually don't have more than a
single tabular element in the reply, and that is the one that goes into the
DataTable. Other tabular elements might contain name-value pairs for a
dropdown list.
ajaxReply does not return from the call, it always exits after the
reply is sent to the client since the envelope ends up closed and further data
would be ignored.
Let's see a typical server page:
<?php
include 'includes/include.php';
ajaxReq();
function ajax_Places_select() {
ajaxReply(201,'Data Follows','select IdPlace as value, Name as text from Places order by Name');
}
Right after including my personal library of functions, I call ajaxReq
then, any number of function definitions may follow. Here I show just one,
ajax_Places_select which will be invoked every time a request comes with ajaxObj=Places&ajaxAction=select
in the URL. This particular function, which I call a 'transaction' since that
is what they usually turn into on the SQL side, does not need any further
arguments so it does not read any more from the URL. It is simply a request to read a
lookup table of places. These values will be used in an HTML select box and the
DataTable formatter for dropdown boxes expects to receive its values as an array of value => text couples,
so I use SQL to make the alias and, in order to have the select box sorted, I do
the sorting at the SQL side, which is very fast. Since the third argument is a
string, it will assume it is an SQL statement so ajaxReply will execute this statement and it will
create an array of literal objects in the data element of the envelope, like:
{
replyCode:201, replyText:"Data Follows",
data:[
{"value":23,"text":"Alice's"},
{"value":1,"text":"Home"},
...
]
}
Other functions might just return a status indication, for example:
function ajax_Places_delete() {
$IdPlace = parse_post_int('IdPlace');
mysql_query('delete from Places where IdPlace =' . $IdPlace)
or ajaxReply(500,'SQL error ' . mysql_error());
mysql_affected_rows() or ajaxReply(501,
'The record does not exist, it might have been deleted by someone else');
}
The delete transaction calls parse_post_int to read argument IdPlace
from the POST request, which it then checks with a regular expresion
for valid
digits and calls intVal to ensure it gets a number. Then it builds an SQL statement to delete a record. If the statement
fails, it sends an error reply with a 500 replyCode and a corresponding
message. It then checks whether it actually deleted any records at all
and, if not, sends another error message. The numbers used for the error
codes are whatever you want, I follow the convention of assuming 5xx are
error codes, I have no standard on what each means, it is just a matter of
documenting them for each transaction. If all goes according to plan, the function will finally return
and ajaxReq which called it in the first place, will then send a 200
'Ok' message with no data. In neither success or failure replies I use
the third argument to ajaxReply since there is no extra information to
convey.
Actually, since the pattern of calling mysql_query and then checking
for errors is so frequent, I use a function called ajaxSqlQuery:
function ajaxSqlQuery($sql) {
$result = mysql_query($sql);
if ($result) return $result;
ajaxReply(620,'Sql error: ' . mysql_error(),array('sql' => $sql));
}
If there is an error in the execution of the SQL statement, mysql_query
will return false so anything else is a valid result, which it then
returns. Otherwise, the reply will have 620 as a replyCode, the
SQL error message as the replyText and an extra property named 'sql'
containing the
failed SQL statement as its value. Not that the end user would care nor even should
know what the structure of your database is but at development time this
information is good.
Another posible transaction on the same table, an update:
function ajax_Places_update() {
ajaxSqlQuery(
BuildSql('update Places set ?1x = ?2s where IdPlace = ?3i and ?1x = ?4s'
, $_POST['fieldName'], $_POST['newValue'],
parse_post_int('IdLugar'), $_POST['oldValue']
)
);
mysql_affected_rows() or ajaxReply(501,
'The record does not exist or it might have been modofied by someone else');
}
ajax_Places_update executes an action query which does not
return a value, nevertheless, the query is made so that it checks that the
record to be changed still exists with its original value (taken from 'oldValue'). I'll describe the
BuildSql function later, right now it is
enough to know that it is a sort of sprintf made for SQL where the ? starts
the placeholder to the values, which follow the formatting list. BuildSql
accepts positional arguments, indicated by the number after the ?, thus
here we use the first argument, fieldName, in two places. It checks if there was any affected rows, if there
weren't it means someone else changed the original record and it returns a 501
error. If none of the above happens, it will return and ajaxReq which
called it, will return a 200,'Ok' message. This kind of update function I
often use for inline edits on DataTable fields. In none of the replies I
use the third argument since there is nothing to return but a status code.
The next function, ajax_Places_insert does a record insert and at the
end it has a call to ajaxReply with the third argument set to an array which is the
database record ID of the newly inserted record.
function ajax_Places_insert() {
ajaxSqlQuery(
BuildSql('insert into Places (Name,Address1, Address2, City,Zip) values (?s,?ns,?ns,?ns,?ns)'
, $_POST['Name'], $_POST['Address1'], $_POST['Address2'], $_POST['City'], $_POST['Zip']
)
);
ajaxReply(201,'Data Follows',array('IdPlace' => mysql_insert_id()));
}
This is how this reply would look:
{"replyCode":201,"replyText":"Data Follows","IdPlace":26}
Function ajaxReq
The ajaxReq reads the ajaxObj argument from $_POST. If
there is no ajaxObj argument, it assumes it was not an AJAX request so it
returns. If it does find one, it does a little regexp check for valid characters
then reads ajaxAction and validates it as well. It finally assembles the function name. If there is such a function,
it will send the headers to prevent the reply from being cached and to insure
the text is read in the proper charset (I don't expect to use anything but Roman
characters, but UTF-8, Cyrillic or any other are reasonable alternatives). Then
it calls the function and on return, if it does return (which it often does not)
, it calls ajaxReply
without arguments, which will produce a 200, 'Ok' reply.
Notice the call to the PHP function set_error_handler right before calling
the variable function $func. This makes PHP send any error to the function
named in the argument. The purpose of this handler is to format any error message in a JSON compatible way. PHP error
messages are formatted in HTML to show on a browser. The function, shown
later, takes the component of the error message and packs them into a JavaScript
object literal. Usually you would save the old value of the error handler to restore it
after whatever you did, but in this case since we are in 'AJAX mode' and we
won't return to 'HTML mode', we don't want the normal handler restored.
function ajaxReq() {
$ajaxObj = trim($_POST['ajaxObj']);
if (strlen($ajaxObj)) {
if (preg_match('/^[a-zA-Z]+$/',$ajaxObj)) {
$ajaxAction = trim($_POST['ajaxAction']);
if (strlen($ajaxAction)) {
if (preg_match('/^[a-zA-Z]+$/',$ajaxAction)) {
$func = "ajax_${ajaxObj}_${ajaxAction}";
if (function_exists($func)) {
header('Cache-Control: no-cache, must-revalidate'); // HTTP/1.1
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Content-type: application/json; charset=utf-8');
set_error_handler('ajaxErrorHandler');
$func();
ajaxReply();
}
ajaxReply(601,'function not defined: ' . $func);
}
ajaxReply(602,'action contains invalid chars: ' . $ajaxAction);
}
ajaxReply(603,'missing action');
}
ajaxReply (604,'object name has invalid characters: ' . $ajaxObj);
}
}
Reading ajaxObj and ajaxAction from $_REQUEST instead of
$_POST is a valid alternative. This might allow you to order
transactions from a GET request as well as from a POST one.
Function ajaxReply
ajaxReply uses two or more arguments. The first two default to a
200, 'Ok' reply message. They are the basic envelope for any
data which, if any, goes into the remaing arguments
beyond the first two.
First, it initializes variable $s where the content of the
envelope will be assembled. It loops through the arguments starting with the
third. It uses the PHP functions func_num_args
and func_get_arg
to check the number of arguments and fetch each one, which will then be stored in $arg.
If $arg
is an array, it will use json_encode
(which in PHP 5 is already built in) to encode it but, since this will produce a
full json-encoded string, with its enclosing curly braces and we might not be
finished yet, we strip those off and concatenate it into $s after a comma
.
If $arg a string then it assumes it to be an SQL statement and
it calls ajaxSqlQuery to execute it. Remember ajaxSqlQuery
already checks for errors and if any is found, it will return the appropriate
reply and die so, if a $result is ever returned, it has to be a valid result
set. I set $s to start a JavaScript array literal named datann,
where nn is a sequential number (though there is no data0
but simply data). Each
element is made of a row returned from the query turned into a JavaScript object
literal made from the field names and field values.
Since the optional arguments from the third on have to be either an array or
a valid SQL statement, anything else will produce an error which, captured by
the error handler set in ajaxReq, will produce a suitable error reply.
Finally, the function assembles the envelope with replyCode, replyText
and the data assembled in $s, if there is any.
function ajaxReply($replyCode = 200,$replyText = 'Ok') {
$s = '';
$nSql = '';
for ($iArg = 2;$iArg < func_num_args();$iArg++) {
$arg = func_get_arg($iArg);
if (is_array($arg)) {
$arg = json_encode($arg);
$s .= ',' . substr($arg,1,strlen($arg)-2);
} elseif (is_string($arg)) {
$result = ajaxSqlQuery($arg);
$s .= ',"data' . $nSql . '":[';
$nextRow = false;
while ($row = mysql_fetch_assoc($result)) {
if ($nextRow) {
$s .= ',';
} else {
$nextRow = true;
}
$s .= json_encode($row);
}
$s .= ']';
mysql_free_result($result);
$nSql++;
} else {
trigger_error("ajaxReply: optional argument at position $iArg value $arg is invalid,
only arrays or SQL statements allowed",E_USER_ERROR);
}
}
echo '{"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '}';
exit;
}
Replying to YUI Get requests
The new YUI Get Utility allows for cross-domain requests, nevertheless it imposses a restriction, it can only read scripts and stylesheets, it cannot read plain data as supplied by a plain JSON server. Though a JSON reply is plain JavaScript, it is simply a value and, unless assigned to something, it is evaluated and completely forgotten.The way to go around this limitation is to provide the server with the name of a callback function so that the reply is actually a function call with the JSON reply as its argument.
In order to do this, lets introduce another URL argument, ajaxCallback which will be the name of the function to call. Then, the function ajaxReply above would end with the following lines:
$ajaxCallback = trim($_POST['ajaxCallback']);
if (strlen($ajaxCallback)) {
header('Content-type: application/javascript; charset=utf-8');
echo $ajaxCallback, '({"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '});';
} else {
echo '{"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '}';
}
exit;
}
If there is an ajaxCallback argument in the request, these last few lines will, first, change the response header from JSON to JavaScript, then echo the very same response enclosed in parenthesis with the name of the function provided in front. This will make the onSuccess callback function of Get unnecesary since upon successful load and evaluation of the response, the callback function provided along the URL will be called and the reply will be handled by that function.
Function BuildSql
This is my BuildSql function which, as I said, it is a kind of sprintf for
SQL. Placeholders start with ? not with % and they are composed of:
?: the start of the place holder
n: optional, a number (starting with 1) for the position of the argument in
the argument list (position 0 is the format string itself). If not
specified, they will be taken in the order in which they appear.
m: optional, either:
m: mandatory: the function will return an error if the data is not present.n: null if zero or zero lenght. Numeric 0, zero length strings, empty date strings will be changed into the SQL literalnull.
t: mandatory, the data type, which can be any of:
i: integers: stringf: floatd: date or datetimet: timeb: booleanp: contents of the global variable$table_prefixx: field name
All the arguments can be either upper or lowercase. The first 6 are quite obvious. Dates are expected as regular PHP
timestamp integer values so it is up to you to convert them from whatever the
format of your date to a PHP timestamp. Strings are
passed through mysql_real_escape_string to escape any might dislike and
enclosed in quotes.
The P argument stands for Prefix and is meant to put before the names of
tables so that in a situation where you have to share the same database with
other applications or development team, the contents of the $table_prefix global
variable, which you would have read from some configuration file, will be put in
front of the table name. For example:
select * from ?ptable
If $table_prefix is empty, then no harm is done, if it is not, then your
table names will share the same prefix.
Finally, the X argument is basically for field names, when the name of the
field itself is a variable. You will have noticed it in the ajax_Places_update function in the example
above, the posted $_POST['fieldName'] contains the name of the field to be
table column to be modified. The field name will be quoted with backticks
as per MySql syntax and escaped. With all this escaping and the intvals,
floatvals and such in the other datatypes, there shouldn't be much chance for
SQL injection.
function BuildSql($query) {
global $table_prefix;
$num_args = func_num_args(); // number of arguments available
$args_used = (1 << $num_args) -2; // bit mask to check if arguments are used
$offset = -1;
$matches = array();
while(true) {
$offset++;
$match = array('start' => $offset);
$offset = strpos($query,'?',$offset);
if ($offset === false) {
$match['len'] = -1;
$matches[] = $match;
break;
}
$match['len'] = $offset - $match['start'];
$state = 0;
do {
$offset++;
$ch = strtolower($query[$offset]);
switch($ch) {
case 's':
case 'i':
case 'f':
case 'd':
case 't':
case 'b':
case 'p':
case 'x':
$match['type'] = $ch;
$state = 99;
break;
case 'm':
if ($state < 10) {
$match['mandatory'] = true;
$state = 10;
} else {
trigger_error('BuildSql: Cannot have an "M" modifier at that position '
. substr($query,0,$offset+1),E_USER_ERROR);
}
break;
case 'n': // null if empty?
if ($state < 10) {
$match['null'] = true;
$state = 10;
} else {
trigger_error('BuildSql: Cannot have an "N" modifier at that position '
. substr($query,0,$offset+1),E_USER_ERROR);
}
break;
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
switch($state) {
case 0:
$match['pos'] = intval($ch,10);
$state = 3;
break;
case 3:
$match['pos'] = $match['pos'] * 10 + intval($ch,10);
$state = 5;
break;
default:
trigger_error('BuildSql: Cannot have a digit at that position '
. substr($query,0,$offset+1),E_USER_ERROR);
break;
}
break;
default:
trigger_error('BuildSql: Unknown formatting character: '
. substr($query,0,$offset+1),E_USER_ERROR);
}
} while ($state < 99);
$matches[] = $match;
}
$arg_pointer = 1; // sequential pointer to arguments
$s = ''; // output SQL statement
foreach($matches as $match) {
if ($match['len'] == -1) {
$s .= substr($query,$match['start']);
break;
}
$s .= substr($query,$match['start'],$match['len']);
// read the value of the argument
if ($match['type'] == 'p') {
$value = $table_prefix; // t is a special case, it takes no argument from the list
$n = 0; // to avoid marking the argument as used.
} else {
if ($match['pos']) {
$n = $match['pos'];
} else {
// else, read the next sequential argument an increment the argument pointer
$n = $arg_pointer++;
}
// some bitwise magic to unset the bit position representing the argument
$args_used &= ~ (1 << $n);
if ($n >= $num_args) {
trigger_error("BuildSql: Missing argument $n after: $s",E_USER_ERROR);
} else {
$value = func_get_arg($n); // otherwise, read it
}
}
if ($match['mandatory'] && is_null($value)) {
trigger_error("BuildSql: Missing value for argument $n after: $s",E_USER_ERROR);
}
$NullIfEmpty = $match['null'];
switch($match['type']) { // now we process $value according to datatype and $NullIfEmpty flag
case 's':
if ($NullIfEmpty and strlen($value) == 0) {
$s .='null';
} else {
$s .= "'" . mysql_real_escape_string($value) . "'";
}
break;
case 'i':
if ($NullIfEmpty and $value == 0) {
$s .='null';
} else {
$s .= intval($value);
}
break;
case 'f':
if ($NullIfEmpty and $value == 0) {
$s .='null';
} else {
$s .= floatval($value);
}
break;
case 'd':
if ($NullIfEmpty and empty($value)) {
$s .='null';
} else {
$s .= "'" . date('Y-m-d H:i:s', $value) . "'";
}
break;
case 't':
if ($NullIfEmpty and empty($value)) {
$s .='null';
} else {
$s .= "'" . date('z H:i:s', $value) . "'";
}
break;
case 'b': // booleans cannot be null
$s .= intval($value!=false);
break;
case 'p':
$s .= mysql_real_escape_string($value);
break;
case 'x':
$s .= '`' . mysql_real_escape_string($value) . '`';
break;
}
}
//
// if all arguments used, $args_used will be zero
if ($args_used) {
for($i=1; $i < $num_args; $i++) {
$args_used >>=1; // bitmask is shifted right
// if right most bit still 1, it means it has not been used.
if ($args_used & 1) trigger_error("BuildSql: Argument $i not used",E_USER_ERROR);
}
}
//
return $s;
}
BuildSql is of general use within or without AJAX, actually, I've been
using it in several incarnations (including Visual Basic and VBScript versions)
for years now. Notice that I use function trigger_error to signal
errors which, with PHP error handler set to ajaxErrorHandler (shown below)
will be reported in true JSON fassion.
Function ajaxErrorHandler
This is the error handler set by ajaxReq to handle error messages in JSON
style. I took it from the example in the PHP manual which discriminates
between the several errors, though it actually doesn't matter much, except to
separate notices from the rest. You have to ignore notices since this
function is called with all errors, warnings and notices, regardless of the
error level you set. Besides setting replyCode and replyText, which are
the least the JSON client expects, it sets other properties from values passed
as arguments to this function.
function ajaxErrorHandler($errno, $errstr, $errfile, $errline) {
switch ($errno) {
case E_USER_ERROR:
echo '{"replyCode":611,"replyText":"User Error: '
, addslashes($errstr) . '","errno":', $errno;
break;
case E_USER_WARNING:
echo '{"replyCode":612,"replyText":"User Warning: '
, addslashes($errstr) . '","errno":', $errno;
break;
case E_USER_NOTICE:
case E_NOTICE:
return false;
default:
echo '{"replyCode":610,"replyText":"'
, addslashes($errstr) . '","errno":', $errno;
break;
}
if ($errfile) {
echo ',"errfile":"' , addslashes($errfile) ,'"';
}
if ($errline) {
echo ',"errline":"', $errline ,'"';
}
echo '}';
die();
}
Licensing: I know company auditors require this so, for their benefit, let me state that all the code above can be freely used, modified, distributed or simply ignored, whichever pleases you most. Just be nice and don't take undue credit for it, if credit is due. Thanks.