1 | 2 | 3 | 4 | 5

1 - 5   [23]

Selecting Next and Previous - MySQL and PEAR::MDB2

Also relates to MySQL and PEAR

Quite a considerable time ago I discussed methods for selecting the next and previous records from a MySQL database using MySQL variables. This method required sending multiple requests to the database via the abstraction layer - at the time I was using PEAR::DB.

Things have progressed a bit since early 2004 with more recent versions of MySQL introducing sub-select queries and PHP5's PEAR::MDB2 abstraction layer offering better handling of parameters in SQL queries. Such that the entire process of selecting the next and previous record can be simplified into a single query. Let us dive straight into the code and then I will break it down:

$query = "SELECT IF(publish_date < :publish_date, 'prev', 'next') as position, title, CONCAT(:archive_dir, permalink) as link FROM news WHERE id IN
            SELECT id FROM news WHERE 
              id = ( SELECT id FROM news WHERE publish_date < :publish_date AND post_status = 'published' ORDER by publish_date DESC LIMIT 0,1 ) 
              id = ( SELECT id FROM news WHERE publish_date > :publish_date AND post_status = 'published' ORDER by publish_date ASC LIMIT 0,1 )

$parameters = array(
  'publish_date' => $this->dataset->publish_date,
  'archive_dir' => self::getDirective('ss_news_archive_dir')
$links = $this->db->getAssoc($query, null, $parameters);

The objective of the above code is to get the next and previous news entries published. So comparison is done on the publish dates relative to the current record.

Preparing to execute the query establishes two parameters (in $parameters) which will be passed to the MDB2 query. The publish date of the current record ($this->dataset->publish_date) is the first parameter while the second parameter is a configuration directive for the current News module.

Note that the actual call to the MDB2 layer ($this->db) requires the use of the Extended module so establishing a connection to the database may be as follows:

$this->db = MDB2::factory($dsn);

Now to the actual SQL query. Working backwards from the inside of the query the following three processes are taking place:

  1. The ID of the previous and next records are retrieved by comparing the publish dates with the current record. This is analogous to the engineering described in the original post back in 2004.
  2. These two results are then fed into a Select query for a single column
  3. Which then allows this column to become the search parameters for the actual SQL query which returns the complete set of data for the next and previous records (using the IN operator)

It is the second phase in the process above which essentially acts as a glue and allows what would otherwise have to be a set of unique queries to be amalgamated into a single query. A typical result from the above code might be:

  [prev] => stdClass Object
    [position] => prev
    [title] => The previous post
    [link] => /newsdesk/previous_post.html
  [next] => stdClass Object
    [position] => next
    [title] => The next post
    [link] => /newsdesk/next_post.html

I forgot to mention from a performance perspective this is very efficient and a few rough benchmark tests showed that execution time was similar to running one of the three (or four) queries required to achieve the same data without sub-selects. Performance could possibly be improved even further, if required, by using an inner join instead of sub-select (but I am yet to try this method).

Posted on Oct 14, 2008 at 12:00:19.

PHP Function: array_merge_assoc

This post does not relate to any other topics

Doing lots of PHP coding at the moment to upgrade my code base for a server migration. One routine I regularly run into is merging data from one array into a map of predefined indexes. For example when POST data is submitted by a form a will fill a map with this data using predefined indexes to ensure no unexpected keys are passed in to exploit the application. The normal approach to this being a foreach loop through the POST data filling in keys that exist as I go. In streamlining my code to use PHP's inbuilt functions I decided to simplify this to a couple of calls to the array function library as follows:

function array_merge_assoc($map, $data) {
  $data_clean = array_intersect_key($data, $map);
  return array_merge($map, $data_clean);

The first call to array_intersect_key strips any invalid keys from the $data array hence making it safe to then merge the two arrays in the second function call.

Posted on Oct 30, 2007 at 12:07:06.

Map MySQL Data to new Schema

Also relates to MySQL

Recently I rewrote the schema for several modules in the hand-rolled CMS I use. As a result I needed to find a simple way to transfer across records from the old schema (imagemanager_old) to the new schema (imagemanager) mapping each old field to the equivalent new field. Since my Mac is running a post version 4.1 MySQL client this was relatively simple using a subselect statement and processing it with PEAR::DB.

$db =& DB::connect($dsn);
$query = "SELECT imid FROM imagemanager_old";
$records = $db->getCol($query);
$prh = $db->prepare('INSERT INTO imagemanager (b1, b2, b3, b4, b5) 
                     SELECT a1, a2, a3, a4, a5 FROM imagemanager_old WHERE imid = !');
$db->executeMultiple($prh, $records);

Where b1…b5 and a1…a5 represent the field names from the new and old schemas respectively. This simple script will invariably come in useful many times as I update and enhance the normalisation of my schemas.

Posted on Oct 23, 2005 at 19:18:37.

PHP Function Lookup from XEmacs

Also relates to Apache and IDEs, X11

Ok, I have gone all XEmacs, and am really impressed. For the best part of two years now I have been using UltraEdit for must web scripting with a little bit of Eclipse on the side for command line testing with PHP5 development. With a large amount of the development work now migrated to my IBook, it was time to seek out a new editor. Currently I am undecided between Eclipse, BBEdit, Vim, Quanta, Kate and XEmacs, although I am very drawn to the latter.

Despite the learning curve, with a bit of Lisp knowledge in hand, the power of XEmacs quickly becomes apparent. Of course an essential feature for any scripting language is syntax highlighting and efficient function lookup. The first is easy enough - add the prog-modes package (which includes an Emacs lisp file for PHP major mode) and all other dependant packages from the package manager. I also enabled syntax highlighting by default in ~/.xemcas/init.el with:

(require 'font-lock)

While perusing the settings for PHP mode from the Options menu (and directly from the package file prog-modes/php-mode.el) I discovered that the key command sequence Ctrl C Ctrl F performs a search for the function currently selected at php.net. This was a problem since the machine is not going to be on a permanant connection. So, I decided to store the PHP Documentation on my local server. Nothing out of the ordinary, except it give me the opportunity to use the RewriteMap directive in the Apache configuration.

  1. Grab the multi-file PHP documentation from php.net. And extract all files to a suitable location on disc (note there are over two thousand separate HTML files and they will all be extracted to same directory, so make sure it is the right place!)
  2. Create a name-based virtual server to access the documentation locally by first adding a new machine name (eg phpdocumentation) to the Netinfo Database in OSX.
  3. Add a virtual server entry to the httpd configuration file. I currently have the system configured to reboot Apache with a different principle conf file for each PHP version - as with my Window 98 box - so I put virtual server directives in one of the user conf files (from /etc/httpd/users) instead.

      ServerName phpdocumentation
      # Rewrite Directives will go here…
      DocumentRoot "/path/to/php/documentation/dir"
      <Directory "/path/to/php/documentation/dir">
        AllowOverride All
        Options +FollowSymLinks
  4. Now, instead of hacking the Lisp code in php-mode.el, which could readily be destroyed by a future update of prog-modes, I decided to throw in a rewrite rule to fix the request URI. From XEmacs the request would be for http://phpdocumentation/function_name, so the following might work:

    RewriteEngine On
    RewriteRule  ^([-_a-z0-9])$ /function.$1.html

    Sadly not! All function help files take the form function.[the-function-name].html. And that is where I hit the stumbling block! Underscores are replaced by hyphens in the HTML file names for functions.

I was reluctant to resort to Lisp hacking, so decided to try one of the rewrite directives I have had little use for previously - RewriteMap.

  1. Create an executable script that can be called in the rewriting process to replace the underscores. I choose Perl and, due the simplicity of the rewrite, only needed one more line of code than the actual Apache manual:

    # php-func-map.pl
    # avoid buffered I/O
    $| = 1;
    while (<STDIN>) {
      # globally change the underscore to a hyphen
      print $_;
  2. Then set up the rewriting directives - for RewriteMap these have to go under the VirtualHost directive in the conf rather than in .htaccess file.

    RewriteEngine On
    RewriteMap php-func-map prg:/path/to/php-func-map.pl
    RewriteRule /([-_a-z0-9]+)$ /function.${php-func-map:$1}.html

And that is that. The magic is in ${php-func-map:$1} which will pass the pattern match $1 to the file defined by php-func-map and get back a PHP manual friendly file name. Of course this could be extended further to perform searches beyond just the manual's function set but servers my purpose well for the time being. So, now when I forget if the needle or the haystack goes first, a quick key combination and the manual entry is available.

Posted on Jun 15, 2005 at 18:58:33. [Comments for PHP Function Lookup from XEmacs- 0]

PHP COM for Outlook to Access

Also relates to Basic and COM

I had an urgency earlier today to produce a script to extract details from the body of a number of emails and put the information into an Access database. For any seasoned VB programmer this would seem like a trivial task I am sure, but having become quite accustomed to using the PCRE library to manipulate and extract text, the thought of becoming embroiled in text searching subroutines using VBA's InStr and Mid functions quickly had me concocting alternatives. If only I could get the body of the emails into some PHP arrays and use the well-seasoned preg family to get the information I needed. Time to call on the Windows COM extension once again.

In fact, getting to an email item is considerably less cumbersome than working with Excel via PHP COM. The messages were stored in a subfolder of the Inbox called Enquiries.

$outlook = new COM("Outlook.Application") or die('….');
$ns = $outlook->GetNamespace("MAPI");
$inbox = $ns->GetDefaultFolder(olFolderInbox);
$subfolders = $inbox->Folders;
while ($folder = $subfolders->getNext()) {
   if ($folder->Name == "Enquiries") {

So now $folder references the subfolder Enquiries and I just needed to cycle through each mail item in this folder to grab the body.

$mail = $folder->Items;
while ($item = $mail->getNext()) {  
  $temp = array();
  $temp['body'] = $item->Body;  
  $temp['date'] = date('d/m/y', $item->ReceivedTime);
  $enquiries[] = $temp;

While I was about it, I also grabbed the date each message was received. Conveniently the PHP COM returns this property as a Unix(!) timestamp so it can be fed straight into date() to get the required output.

A few regular expressions later and the data was transformed into an SQL string ready to feed into MS Access. Sticking with the Windows theme, the inbuilt ODBC functions seemed the best bet for this, once Null values had been handled and a System DSN established.

I am in no way promoting this as a method to go about transfering Outlook data to Access. It just demonstrated the flexibility PHP can offer. I needed to do some heavy duty work on the body text to create the field/values I needed and since PHP is the language I use on a daily basis, I found it less time consuming to build this bridge than to dust off and start trawling through old VBA references.

This is one of many areas where Thunderbird shines for me - emails stored in text format ready to be grep'ed.

Posted on Nov 19, 2004 at 03:37:36. [Comments for PHP COM for Outlook to Access- 0]

Breadcrumbs Trail

[ Home ] -> TW Blog -> PHP
Site Map

The Severn Solutions website achieves the following standards:

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

Page compiled in 0.008 seconds