1 | 2 | 3

1 - 5   [14]

String Concatenation in MySQL Aggregate Queries

This post does not relate to any other topics

For a minute I was thinking about building a stored procedure to handle this until I had a quick read up on the aggregate functions in the MySQL manual!

In brief I wanted to get a list of names from a one-to-many relationship - in this case multiple owners for a single property. These needed to be displayed as a single record in a form field and ideally the information should be drawn from the database in the same query as all the other information for the property. When dealing with numeric data, summation or unit count of the many side of a one-to-many relationship is quite simple using the aggregation functions (eg SUM() and COUNT()). As it turns out a similar function is available for strings (as of MySQL 4.1) and this is GROUP_CONCAT().

So for example I have:


====== data_properties ======
prop_id     10
prop_name   "This House"
=============================

======= data_owners =========
owner_id     12
owner_name   "Joe Blogs"
=============================
owner_id     13
owner_id     "Phil Blogs"
=============================

= data_properties_to_owners =
prop_id      10
owner_id     12
=============================
prop_id      10
owner_id     13
=============================

And I want to get a result set along the following lines:


Object {
  prop_id => 10
  prop_name => "The House"
  owners => "Joe Blogs, Phil Blogs"
}

In a single query we can do this using the following DML query:


SELECT 
  a.prop_id, a.prop_name, 
  GROUP_CONCAT(
    DISTINCT b.owner_name 
    ORDER BY ASC
    SEPARATOR ", "
  ) as owners
FROM 
  data_properties as a, 
  data_owners as b,
  data_properties_to_owners as c
WHERE 
  a.prop_id = c.prop_id and c.owner_id = b.owner_id
GROUP BY a.prop_id

So this query would take the following dataset:


=== prop_id ==  prop_name  ==  owner_name   ===
===    10   == "The House" ==  "Joe Blogs"  ===
===    10   == "The House" ==  "Phil Blogs" ===

And groups it on the last field to produce the required resultset. This reduces the demands on the PHP API to manipulate the data from the resultset prior to delivery to the templates.

GROUP_CONCAT() function has several parameters which can help further manipulate the resultset to get exactly the data required. See the aggregate functions in the MySQL manual for more information.

Posted on Jan 21, 2009 at 11:19:48.

Selecting Next and Previous - MySQL and PEAR::MDB2

Also relates to PHP 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 ) 
                OR
              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);
$this->db->loadModule('Extended');
$this->db->setFetchMode(MDB2_FETCHMODE_OBJECT);

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:


Array
(
  [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.

Map MySQL Data to new Schema

Also relates to PHP

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.

Resequence a MySQL column

Also relates to PEAR

The other day I was asked if I knew a way to resequence a column in a MySQL table. For example if a record were removed from mid-table, how to collapse all the ids above that record to avoid gaps. Of course this would be highly undesirable and potentially destructive if relationships exist, but made an interesting little problem.

The initial solution I suggested, utilising the PEAR_DB package as abstraction layer for the PHP MySQL API, was as follows:


$id = {num}; // the id of the record that was deleted
$pr = $db->prepare('UPDATE table_name SET col_name = ! WHERE col_name = !');
do {
  $res = $db->execute($pr, array($id, ++$id));  
  if (DB::isError($res)) {
    // handle error
    break;
  }
} while ($db->affectedRows() > 0);

Errors are trapped to allow break-out from the loop, and, since $db->affectedRows() can return -1 if the query fails, look for at least one (will be only one per loop execution) affected row.

While this seemed like a nice simple solution, it doesn't account for a column that AUTO_INCREMENTs, so assumes that values for col_name are entered manually. Then, courtesy of the MySQL cookbook, I stumbled across a more efficient solution that takes into account just that by utilising the DDL component of the MySQL query language:


$query = "ALTER TABLE table_name "
       . "DROP col_name, "
       . "ADD col_name INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, "
       . "AUTO_INCREMENT = 1";
$res = @mysql_query($query, $conn);
if (!$res) { // handle errors }

For simplicity, this just uses the standard PHP MySQL API. This will recreate col_name placing it first in the schema order (FIRST). Thus recreating the sequence starting the increment from 1 (AUTO_INCREMENT = 1 - prior to 3.23.39 only). Finally, there is no need to redefine the PRIMARY KEY if it is col_name, since MySQL does not drop the key during the execution of a single ALTER statement.

Personally I cannot think of any reason I would have to resequence a column, especially if it were the PRIMARY KEY, but nice to know it is possible?

Posted on Apr 16, 2005 at 14:51:16. [Comments for Resequence a MySQL column- 0]

Inexplicable Takeover

Also relates to Security

This week has been full of mind boggling puzzles no more so than the apparant take over of my machine by the Outpost Firewall! I run MySQL 3.23 Daemon on request and mid-week for no apparant reason I could no longer connect to the server after booting up. After running a few diagnostics, I concluded that somehow MySQL had corrupted, so since I had a zipped copy of MySQL 4.0.21 on disc I gave this a spin - same problem. At a loss, and concern growing due to total dependency for current work, I tried reinstalling the TCP/IP software. No joy. So I decided to take a peek on the web for similar problems. This entailed booting up the Outpost Firewall, which I have been test driving the past few weeks. To my surprise, once the firewall was running, I could connect to the MySQL server as normal! This was bewildering to me. I boot my system with no services running and the minimal Windows programs, so it would seem somehow MySQL had become inextricably dependant on Outpost!? Eventually I managed to restore normality by cleaning out the shared components in Outpost, but why this worked I can not explain.

So, now I am at a loss on a choice of firewall. Since removing Norton, I have tried numerous firewalls over the past few months - BitDefender, Kerio, Sygate, ZoneAlarm and now Outpost. Prior to this mishap, Outpost had actually been the first one that had not burdened me with conflicts, crashes or reduction in performance. Most frustrating is I would go back to BitDefender immediately if they could only resolve the Windows 98 bugs.

The one positive outcome from this puzzle is that I now have access to MySQL 4 on demand. I generally build on 3.23 to match the live servers I use. Using the datadir parameter in two option files, and booting the daemon from a batch file I can switch between each version as I please. Add to this the three versions of PHP4 and latest PHP5 I currently choose from when booting Apache, and Windows 98 is really not all that bad for development!

Posted on Jan 30, 2005 at 21:37:21. [Comments for Inexplicable Takeover- 0]

Breadcrumbs Trail

[ Home ] -> TW Blog -> MySQL
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