Blog of a LAMP Developer based near Guildford, Surrey

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;

After plenty of research and trials, it is possible to use mysql transactions for custom queries.

You still have to create the table with InnoDB for row based locking to work.

ALTER TABLE table_name type=InnoDB;

And set the transactions variable in your model

var $transactional = true;

Now in your method use the following:

class YourModel extends AppModel {

var $transactional = true;

function mymethod()
{

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

 #start the transaction
  $db->begin($this);

 $success = $db->query('my sql');
  if ($success  === true) {
       return $db->commit($this);
  } else {
       $db->rollback($this);
       return false;
  }
}

}