1 | 2

1 - 5   [10]

SQLite Custom Functions In PHP 5

Relates to PHP and Databases

I cannot get enough of the new features in PHP 5, and the SQLite extension is no exception. Besides the speed and simplicity, one of the most interesting features is the ability to define custom functions for direct use in the SQL statement.

In a content based public domain application, dynamic creation generally involves three steps:

  1. Retrieve the data from a database
  2. Use array functions to format the data for display (beyond what the SQL layer's extended functions offer)
  3. Plug the data into the appropriate markup using some form of template system

The sqlite_create_function() effectively removes the need for step 2 altogether - instead define and associate formatting functions with the sqlite_db object and use them in the SQL statement.

For example, regularly I will use an image_manager table to store references to image files across a broad range of content. With MySQL, the CONCAT() function allows concatenation of the image file name (stored data) and the directory path (parameter) within the resultset. But the resultset must still pass through a formatter function to confirm the existence of the image, and get additional information for the file (eg the height and width). In contrast, with SQLite, the formatter function is defined and attached directly to the sqlite_db object.

function get_image_file($data) {
  $file = IMG_DIR . $data . ".jpg";
  if (is_file($file)) {
    $stats = getimagesize($file);
    $str = "<img src=\"/g/$data.jpg\" "
         . "width=\"$stats[0]\" "
         . "height=\"$stats[1]\" />";  
  else {
    $str = "NO FILE";  
  return $str;  
$db = new sqlite_db("db.content");
                     "get_image_file", 1);

Now the SQL statement can include the user-defined function to retrieve the image files directly into the resultset.:

$res = $db->array_query(
         "SELECT image_desc, "
       . "get_image_file(image_file) as image "
       . "FROM image_manager "
       . "WHERE image_ref = $param", SQLITE_ASSOC);

The resultset $res is now ready to send direct to the appropriate markup for display. This powerful utility has a range of uses from advanced content parsing, for example with PEAR::HTML_BBCodeParser, to just replicating some of the advanced SQL functions of other RDBMS with added user flexibility.

The ability to use PHP from within SQL allows you to simplify the actual script making it accessible to larger audience of developers. This allows PHP to be used as a templating engine that simply populates HTML structures with database data. In many instances this can simplify the code so much that there is no need to place a templating system on top of PHP. Beyond the code simplification, this also improves performance and reduces the script's memory footprint, since no data manipulations need to occur in user-space. SQLite Introduction, By Ilia Alshanetsky

Here are some useful links to more SQLite related pages.

Posted on Mar 29, 2004 at 01:09:38. [Comments for SQLite Custom Functions In PHP 5- 0]

Enforce Referential Integrity With DataObjects

Relates to PHP and PEAR, Databases

The PEAR::DB_DataObject package is rapidly becoming an integral part of my development arsenal. Building small scale content management becomes surprisingly simple, producing code in the CMS API layer that is concise and clear. Here is a little walk through comparing this package with its uncle - PEAR::DB - for batch deletions.

Take a simple shopping cart that includes customer and order entities as part of a wider database. These are inevitably linked in a one-to-many relationship on the customer_id field. As part of a spring clean, we want to remove all customer records that do not have an associated order record (i.e. all customers that have not placed any orders), while enforcing referential integrity. A typical DML method to achieve this is a LEFT JOIN as follows:

  customers LEFT JOIN orders
  customers.customer_id = orders.customer_id
  orders.customer_id = NULL  

The following snippet uses PEAR:DB to perform the batch deletion, based on the result of the above SQL:

$db =& DB::connect(ss_dsn());
$query = "SELECT customers.customer_id "
       . "FROM customers LEFT JOIN orders "
       . "ON customers.customer_id = orders.customer_id "
       . "WHERE orders.customer_id IS NULL";
$result = $db->getAll($query);
$prh = $db->prepare('DELETE FROM customers WHERE customer_id = !');
$sth = $db->executeMultiple($prh, $result);

Here, the getAll() method is used to return the set of results as an array of arrays, so they can be plugged directly into the executeMultiple() method with a prepared statement. The same result could be achieved by walking through the result set deleting each record in turn. For example:

$result = $db->query($query);
while ($result->fetchInto($record, DB_FETCHMODE_OBJECT)) {
  $db->execute($prh, array($record->customer_id));

Ok, now let us walk through the process using DB_DataObject. The approach is quite different. One of DataObject's main features is to make the DML virtually transparent, and rather than working with field values stored in arrays, we are working directly with the entities mapped onto objects.

  1. Create an instance of the customer object
  2. All customers records need to be examined, so set a select condition to retrieve all records
  3. Create a result set by executing the find() method
  4. Iterate over each customer and test for the existence of a link by attempting to create a linked orders object
  5. If the test fails, remove the customer

Here is the snippet:

$customer =& DB_DataObject::factory('customers');
$customer->whereAdd('user_id > 0');
while ($customer->fetch()) {
  if(!$customer->getLink('customer_id', 'orders', 'customer_id')) {

Removing the SQL from this layer makes the code considerably less error prone, especially if the relationship is threefold or greater. This approach can be readily adapted to the case of a single deletion. To ensure referential integrity will not be violated when removing a record, SQL statements must be executed against all entity classes holding a relationship with the current record. With DataObjects the database queries are executed internally, and all the current layer needs to do is test for link objects. Multiple relationships can actually be stored in a links.ini configuration file, and the integrity test achieved by simply calling the getLinks() method.

The release of PHP 5 Release Candidate 1 sees a dramatic step forward for PHP into Object Orientated design and development. PEAR::DB_DataObjects complements this nicely.

Posted on Mar 27, 2004 at 04:51:18. [Comments for Enforce Referential Integrity With DataObjects- 0]

View PHP Source

Relates to PHP and Apache, DOM Scripting

Here is a cute little bookmarklet to tie in with the source file for viewing underlying PHP as outlined in the PHP Manual. The source file should be placed in the server root directory for the main server or a virtual host, and the Location directive set accordingly in the Apache configuration file (See the PHP Manual). This code snippet simply performs a regular expression replace to change the location.href to point to the source file.

javascript:var re=/\/\/([-a-z\.]+)\//i; _
window.location.href= _

This code can be pasted into a new bookmark ( _ just represents line continuation), or drag this PHP Source link onto your bookmarks. I find this quite useful when walking through a site on my test platform, and it complements the many client side developer utilities in the Firefox Web Developer toolbar and these comprehensive Web Development Bookmarklets.

Posted on Mar 27, 2004 at 04:49:49. [Comments for View PHP Source- 1]

PHP 5 On Demand

Relates to PHP and Apache, DOS

For all its anachronistic tendencies, my Windows 98 test platform is surprisingly robust when a new challenge arises. I have been itching to try out the PHP 5 Release Candidate 1, and have had the binary on my desktop for the past few days waiting to be unzipped. My sole apprehension was an install that did not interfere with the current PHP 4 configuration and all the in-progress work that relied on it. Acquiring a few tips from the articles already available on concurrently running PHP 4 and 5, the solution was actually quite simple and very quick to initiate.

Since I only boot Apache server on demand, all that was needed was a separate httpd configuration file to load the PHP 5 Apache module, with updated <a href="http://httpd.apache.org/docs/windows.html#cmdline" title="Online Apache Documentation for LoadModule Directive">LoadModule</a> and <a href="http://httpd.apache.org/docs/mod/core.html#addmodule" title="Apache Documentation for AddModule Directive">AddModule</a> directives:

LoadModule php5_module c:/php5/php5apache.dll
AddModule mod_php5.c

Then it was just a case of handling the php.ini file. When PHP is run as a module in Apache, the ini file must be either located in the Apache root folder or the System root folder. I did not want to interfere with the current ini file for the PHP 4 configuration (already located in the System root folder), and wanted to avoid performing a rename/copy/paste action every time Apache was booted with PHP 5.

So I placed the PHP 5 ini file in the Apache root folder (read before the System root folder) under a different name, and set up the following batch file to rename it before booting Apache with the alternate httpd configuration file:

RENAME php.ini.v5 php.ini
"C:\Program Files\Apache Group\Apache\Apache.exe" _
  -f "C:\Program Files\Apache Group\Apache\conf\httpd.php5.conf"

The reverse is required when Apache is shut down, to ensure the version 5 ini file is not called next time Apache is booted with version 4:

CD "C:\Program Files\Apache Group\Apache"
apache -k shutdown
RENAME php.ini php.ini.v5

Finally, with a shortcut to each of these two batch files, placed somewhere nice and accessible, I can now revel in the delights of the newly improved OOP and XML features, and the in built SQLite database, and revert back to PHP 4 when work needs to be completed.

Posted on Mar 27, 2004 at 04:48:14. [Comments for PHP 5 On Demand- 0]

PEAR HTTP_Session Package

Relates to PHP and PEAR

A few months ago I discussed a method I have adopted for PHP session management. It is a simple process that uses a forced server redirect the first time a user arrives at a page to confirm cookies are enabled for session storage. This allows a message to be displayed to the user if cookies are disabled rather the application failing at the next step (eg after login/registration submission).

I have recently been updating my object-oriented PHP CMS with PEAR packages, to reduce the coding and maintenance I have to perform. The HTTP_Session module is no exception. This is a lightweight interface for all the inbuilt session management functions and as a class it can be readily extended with added functionality. The PHP CMS only stores a session variable for login details to persist the administrator across multiple HTTP requests, so I only really have need for the get and set methods.


$user =& new CMS_User;

if (HTTP_Session::get("user")) {
  $user->loggedIn = TRUE;
elseif ($user->requestLogIn()) {
  HTTP_Session::set("user", $user->getDetails());

At the same time, I was keen to extend the class with the cookie test described above.

class CMS_Session extends HTTP_Session
  function test()
    if (HTTP_Session::isNew()) {
      if (HTTP_Session::detectID() == NULL) {
        if (!isset($_GET[HTTP_Session::name()])) {         
          $sess_id = substr(SID,(strrpos(SID,"=")+1));
          $redirect_url = CMS . CMS_REDIRECT_QUERY . $sess_id;
          header("Location: " . $redirect_url);
        else {
          $old_id = $_GET[HTTP_Session::name()];
          HTTP_Session::start("CMS", $old_id);
            new CMS_Session_Error(CMS_ERROR_COOKIES_DISABLED)
    return true;    

This method is called statically following CMS_Session::start() on the login page. Since the file serves the welcome page for the CMS as well as login, HTTP_Session::isNew() is tested to allow users with an established session to skip straight out of the method. If the session is new and a session ID can not be detected (all users on first call to the page), the forced redirect will take place with a GET query string. On reloading, if the session ID still can not be detected, the user does not have enabled cookies (the GET query string confirms it is a reload). Therefore, the session can be destroyed and an error displayed to the user - in this example by throwing a CMS_Session_Error.

Note that the built-in SID constant does not need to be passed in the query. Where the session does not persist (cookies disabled) a second session will be created following the redirect. Passing the query simply allows both sessions to be destroyed - Store the (original) session ID from the GET query, destroy the new session, regenerate the old session with the original session ID and destroy that. This is not really necessary, since lingering invalid sessions can be cleared out regularly from the temporary directory or session database using a cron job.

A further extension to HTTP_Session can accomodate applications running on virtual (shared) servers by hash encoding the session data to both scramble the stored information and prevent the packet from being intercepted and changed during tranmission.

function setEnv($var, $val)
  $str = $var . "-" . $val;
  $str = base64_encode($str . "," .
                       md5($str . CMS_SESSION_HASH));                    
  HTTP_Session::set($var, $str);       

function getEnv($var)
  $str = HTTP_Session::get($var);
  $str = base64_decode($str);   
  list($details,$session_hash) = split(",",$str);   
  if (md5($details.CMS_SESSION_HASH) == $session_hash) {
  list($var, $val) = explode("-", $details);
  return $val;

This is a simple encoding example that could be used for immutable session data. (For example user login details) A copy of the session variable is retrieved in the get method, to ensure any manipulation is not performed directly on the value of the session variable.

HTTP_Session also offers a range of pre-built functionality including database storage and idle and expiration time management.

Posted on Mar 07, 2004 at 20:49:56. [Comments for PEAR HTTP_Session Package- 1]

Breadcrumbs Trail

[ Home ] -> TW Blog -> Archives for March 2004
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.009 seconds