1 | 2

1 - 5   [7]

HAVING Clause in MySQL

Also relates to MySQL

Point of note that an aggregate function is meaningless in the WHERE clause of an SQL statement. Common sense really considering the return from the function cannot be calculated until the rows have been returned. I discovered this on trying to retrieve a set of distinct categories based on the existence of at least one visible record classified in that category. The solution uses the HAVING clause.


SELECT lookup.category
FROM data LEFT JOIN lookup
ON data.cid = lookup.cid 
WHERE data.visible = 1 
GROUP BY lookup.cid 
HAVING count(data.id) > 0 
ORDER BY cname ASC

Posted on Oct 03, 2004 at 02:47:27. [Comments for HAVING Clause in MySQL- 0]

SQLite Custom Functions In PHP 5

Also relates to PHP

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");
$db->create_function("get_image_file",
                     "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

Also relates to PHP and PEAR

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:


SELECT
  customers.customer_id
FROM
  customers LEFT JOIN orders
ON
  customers.customer_id = orders.customer_id
WHERE
  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');
$customer->find();
while ($customer->fetch()) {
  if(!$customer->getLink('customer_id', 'orders', 'customer_id')) {
    $customer->delete();
  }
}

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]

Selecting MySQL Next And Previous Revisited

Also relates to MySQL

The other day I discussed a simple technique for selecting the next and previous records from a MySQL database using variables. Here is a little tip for extending this to limit the results to a defined number of most recent entries.

Having selected the unique ID for the next and previous records, as discussed in the previous blog entry, another variable can be defined which counts the number of records posted more recently than the previous record. Then the previous entry is only returned if it is within a predefined boundary for recent entries. Here is the full SQL:


# set the boundary to the most recent five results
SET @lower_bound := 5;

# set the id of the previous entry
SELECT @prev := uid 
       FROM table 
       WHERE uid < {x} 
       ORDER by uid DESC 
       LIMIT 0, 1;

# set the id of the next entry
SELECT @next := uid 
       FROM table
       WHERE uid > {x}
       ORDER by uid ASC
       LIMIT 0, 1;
       
# get the number of records posted more 
# recently than the previous record
SELECT @records := COUNT(uid)
       FROM table
       WHERE uid > @prev
       ORDER by uid DESC;

# create a return result array, but only include
# the previous record if it falls within the boundary
# of most recent records
SELECT IF(uid = @prev,'p','n') as pos, uid, title 
       FROM table
       WHERE (uid = @prev AND @records < @lower_bound) 
              OR uid = @next

A typical application of this solution is distinguishing latest news items from the news archive in a small scale News Desk (i.e. where new items may only be added occasionally so the date is not a reliable factor for determining the latest posts). By integrating a couple of simple mod-rewrite rules in the configuration or .htaccess file, and a cache module (eg PEAR::Cache_DB) to compensate for the additional database overhead a complete News Desk with latest items and archive items can be readily simulated through a single source.

Posted on Feb 08, 2004 at 04:30:40. [Comments for Selecting MySQL Next And Previous Revisited- 1]

MySQL Summarizing

Also relates to MySQL

Another little SQL snippet here for MySQL. This one is useful when summarizing <select> data from an HTML form (perhaps stored as an ENUM datatype).


SELECT @total := COUNT(*),
       @offices := COUNT(IF(type='offices',1,NULL)),
       @units := COUNT(IF(type='units',1,NULL))
       FROM `tblunits`;                   
SELECT CONCAT(@total, ' [', 
              @offices, ' office', 
              IF(@offices != 1,'s',''), ', ', 
              @units, ' unit', 
              IF(@units != 1, 's',''), 
              ']');                    

Once again these two queries utilise variables, and make use of conditional testing within a COUNT function to calculate the summaries. The CONCAT could just as easily have been done in PHP, but I chose to use the PEAR::Package::DB getOne() call. Short and simple!

Posted on Nov 14, 2003 at 03:42:03. [Comments for MySQL Summarizing- 0]

Breadcrumbs Trail

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

The Severn Solutions website achieves the following standards:

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

Page compiled in 0.006 seconds