TW Blog :: Entries for MySQL

The wisest man is the man who knows he knows nothing Socrates

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 18: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 13: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]

HAVING Clause in MySQL

Also relates to Databases

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 01:47:27. [Comments for HAVING Clause in MySQL- 0]

MySQLi In PHP5

Also relates to PHP

PHP5 continues to serve up a fine array of new features. Having already sampled and savoured the very extensible SQLite, I have just stumbled across the new MySQLi (Improved MySQL) extension, which brings an object orientated interface and lots of exciting new enhancements to the MySQL library (while still supporting the older procedural interface). Get stuck in with these Zend articles: