Planet CakePHP

I used to go down the road of doing a for each on my source table, which I always hated and thought was bad.
This way I can use table locking for MyISAM or transactions, for InnoDB, to make sure the data gets copied across.
I mainly use this method for archiving tables and keep the primary keys the same.
I have 2 tables created called ads and adsarchives.
What you do is make a copy of your database structure called adsarchives, and change the primary key so that it doesn’t auto increment. This mean that you can’t copy the same data twice. Also the second query won’t delete your data from the source on a fail.
( Oh by the way, my first example is using Cakephp on a MyISAM table) It would be much better to do transactions for this. But I wasn’t updating an InnoDB table.

function archiveads($monthsold=false){

if(is_numeric($monthsold) & $monthsold > 1){

$range = date('Y-m-d H:i:s', strtotime("-{$monthsold} months"));

$db =& ConnectionManager::getDataSource($this->useDbConfig);

$this->query('LOCK TABLES ads READ, adsarchives WRITE');
$this->query("INSERT INTO adsarchives SELECT ads.* FROM ads WHERE ads.enddate < '{$range}'");

$error = $db->lastError();

$this->query('UNLOCK TABLES');

if(!$error) { $this->query("DELETE FROM ads WHERE ads.enddate < '{$range}'") ; return true;}else{

$this->log('FATAL Error archiving the Ads |'.$error);

}

}else{

return false;
}

}

If you want the raw SQl way of doing it try this. I’m archiving 3 month old data.

LOCK TABLES ads READ, adsarchives WRITE;
SET @age=(CURRENT_DATE()- INTERVAL 3 MONTH), @today=CURRENT_DATE();

INSERT INTO adsarchives
SELECT ads.*
FROM ads WHERE ads.enddate < @age;

UNLOCK TABLES;
DELETE FROM ads WHERE ads.enddate < @age;