TW Blog :: Archives for October 2005
..so long as we are given up to the throng of desires with its constant hopes and fears... we never obtain lasting happiness or peaceArthur Schopenhauer
Map MySQL Data to new Schema
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 Sunday, Oct 23, 2005 at 18:18:37.
