1

1 - 4   [4]

Result Sets From Different Sources

Relates to PHP

Continuing the discussion of cleaner programming layers, one problem I find with similar sets of results from different tables, is that the API layer becomes cluttered with functions formatting similar result sets, with negligible differences. For example links to different sections of this weblog (the categories and archives). A quick solution to this is to set generic field names for the result set in the SQL query, and then using the same function to format the data in an array_walk call.


function ss_fns_build_list(&$str_item)
{
  extract($str_item);
  $str_item = sprintf("<li><a href=\"%s\"%s>%s</a></li>\n",
                      $t_url, $t_title, $t_text);
}

function ss_fns_get_data($str_query)
{  
  $var_results = ss_db_sel($str_query);
  if (PEAR::isError($var_results)) { // error trap }
  else
  {
    array_walk($var_results, 'ss_fns_build_list');
    return implode("\n", $var_results);
  }
}

The data is initially formatted in the SQL query (not shown) and assigned to generic field names (t_url, t_title, t_text). Then each item of the result set array is passed by reference to the function ss_fns_build_list where it is reformatted as a string. Then the entire array can be returned as a single string by first calling implode(). The PHP Manual states that modifying the array from inside the function may cause unpredictable behaviour, however I have not witnessed any testing the above code. A safety measure would be to pass a third parameter to the function by reference and fill that with the newly formatted data.

This solution is satisfying since it is visibly extensible. As an example, suppose the result set has groups of data (eg links to websites, categorised by region). Suppose, for this example, the grouping category has been given the generic field name t_list in the SQL query. Now an additional function can be defined at the API layer to do the additional formatting.


function ss_fns_build_list_multiple(&$arr_item, $key, &$arr_sets)
{
  $str_key = $arr_item["t_list"];
  ss_fns_build_list(&$arr_item);
  $arr_sets[$str_key][] = $arr_item;  
}

function ss_fns_get_grouped_data($str_query)
{
  $var_results = ss_db_sel($str_query);
  if (PEAR::isError($var_results)) { // error trap }
  else
  {
    array_walk($var_results,
               'ss_fns_build_list_multiple',
               &$arr_sets);
    foreach($arr_sets as $str_group_header => $arr_list_items)
    {
      $str_output .= "$str_group_header\n" 
                   . implode("\n", $arr_list_items) 
                   . "\n";
    }
    return $str_output;
  }
}

Each item of the result set is passed by reference to the function ss_fns_build_list_multiple. The category is looked up in the field t_list and then the item is formatted using the original ss_fns_build_list function. Finally it is assigned to the correct category in the third parameter, $arr_sets, which is also passed by reference. All that is left to do is to loop through the key, value pairs to produce the return data.

I have found these generic functions can be effectively applied to a number of scenarios, thus reducing the volume of code in the API. One further development would be to pass the location of a template as the third parameter in array_walk and hence remove the HTML from the layer completely, allowing more flexibility.

UPDATE - 2004-02-25 I have recently switched off the allow_call_time_pass_reference configuration. The function calls in ss_fns_build_multiple_list and ss_fns_get_grouped_data should not declare the variables $arr_item and $arr_sets, respectively, be passed by reference (this is left to the function being called). Under the new configuration, these call-time pass-by-references produce warnings. Thanks to the PHP Anthology for pointing this out to me.

Posted on Jan 30, 2004 at 00:46:40. [Comments for Result Sets From Different Sources- 0]

Selecting Next And Previous

Relates to PHP and MySQL

Starting to get things sorted with the new look business site now, so time to catch up on some blogging. I am going to start off by looking at the common scenario of creating next and previous links for a data result set.

A typical application of the next and previous links is in a News Archive. These links offer intrinsic usability benefits presenting the user with less steps to view articles (eg they do not have to jump back to an index to find the link for the next article). However, selecting neighbouring items from a MySQL database is not that simple.

The obvious solution is to select the item with a unique key one less/more than the current record. But this has two drawbacks. Firstly it requires integers as unique keys, which is not always the case. It also assumes that there is a continuous sequence of unique keys with no breaks. This is rarely the case in a content management system, where some items might be deleted.

For this example I am going to assume that the unique key is not integer based, and instead focus on the timestamp at which each entry was added. So rather than adding or subtracting directly from a unique key, all that is required are a couple of comparative selection queries, having firstly established the timestamp for the current record.


SELECT @now := added 
       FROM table 
       WHERE uid = {x};
SELECT @prev := uid 
       FROM table 
       WHERE added < @now 
       ORDER by added DESC 
       LIMIT 0, 1;
SELECT @next := uid 
       FROM table 
       WHERE added > @now 
       ORDER by added ASC 
       LIMIT 0, 1;

This in itself is one solution, if, rather than creating SQL variables, the required data was just extracted directly in the second and third statements. But this will require two separate calls to the database abstraction layer from the API, with two sets of result data to manipulate. The code can be made more manageable at each layer if this is reduced to a single call. Having acquired the unique ID of the next and previous item, it is merely a case of collecting the data.


SELECT IF(uid = @prev,'p','n') as position, uid, title 
       FROM table 
       WHERE (uid = @prev) OR (uid = @next);

In this statement I have created an additional result field position which will be set to p or n for the previous and next records respectively. This is because I generally use PEAR::DB as my database abstraction with a set of custom functions. In this scenario, the first three statements can be executed in the standard way, $dbh->query($query), and the final statement called via $dbh->getAssoc($query). This will return an associative array of a maximum two items with keys of either n or p and the corresponding data.

Of course there is always more than one way of doing things, and I feel the logic of this alternative approach quite elegant, however, with limited permissions on my current servers, I can only test this on my local machine.


SELECT @row := 0;
DROP TABLE IF EXISTS temptable;
CREATE TEMPORARY TABLE temptable 
       SELECT @row := @row + 1 as rownum, table.* FROM table
       ORDER BY added DESC;
SELECT @current := rownum 
       FROM temptable 
       WHERE uid = {x};
SELECT IF(rownum < @current, 'n', 'p') as position, uid, title
       FROM temptable 
       WHERE (rownum = @current - 1) OR (rownum = @current + 1)

The code utilises the CREATE TEMPORARY TABLE statement to create a replica of the data that is being examined, and adding an additional field to the temporary table to act as a unique integer identifier (rownum). This resolves the problem outlined initially regarding the datatype of unique indexes, and sequence gaps.

There are no doubt copious other solutions to this problem. Of course an obvious choice is to do the work on the API layer. For example, the entire dataset could be collected and then PHP's array functions used to manipulate the dataset and extract the required information. However, I have found in the past this can get quite kludgey! Doing the work at the Database layer itself does not (appear to) harm the performance of the database, and helps keep all layers in the application that bit purer.

Posted on Jan 30, 2004 at 00:45:22. [Comments for Selecting Next And Previous- 4]

Undergoing Changes

Relates to Peregrinations and Blogging

A Quick notification…. Currently undergoing a number of changes on my web site and the presentation of my blog. During this time, the site, blog or certain pages may be unavailable….

Posted on Jan 20, 2004 at 19:23:19. [Comments for Undergoing Changes- 2]

Happy New Year

Relates to Peregrinations

A Happy New Year and best fortunes for 2K4!

Posted on Jan 02, 2004 at 13:40:29. [Comments for Happy New Year- 0]

Breadcrumbs Trail

[ Home ] -> TW Blog -> Archives for January 2004
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