Hey Y!!
For my current project I needed to perform a full database backup. I took the following code from David Walsh and adapted into usage in Yii. I wanted to use the existing Yii::app()->db connection instead of having to create a new one using mysql_connect($host,$user,$pass). I have the function in a Helpers class at the moment. Example usage:
Helpers::backupDb(’/home/user/backups/db.sql’); // Performs full backup
Helpers::backupDb(’/home/user/backups/db.sql’, ‘Users’); // Backs up Users table
Helpers::backupDb(’/home/user/backups/db.sql’, ‘Users, Orders, Categories’); // Multiple tables specified
Helpers::backupDb(’/home/user/backups/db.sql’, array(‘Users’, ‘Orders’, ‘Categories’); // Does the same as above
/*
* PHP MySQL Backup Function
* Adapted to Yii from David Walsh's code http://davidwalsh.name/backup-mysql-database-php
*/
public static function backupDb($filepath, $tables = '*') {
if ($tables == '*') {
$tables = array();
$tables = Yii::app()->db->schema->getTableNames();
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
$return = '';
foreach ($tables as $table) {
$result = Yii::app()->db->createCommand('SELECT * FROM ' . $table)->query();
$return.= 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = Yii::app()->db->createCommand('SHOW CREATE TABLE ' . $table)->queryRow();
$return.= "\n\n" . $row2['Create Table'] . ";\n\n";
foreach ($result as $row) {
$return.= 'INSERT INTO ' . $table . ' VALUES(';
foreach ($row as $data) {
$data = addslashes($data);
// Updated to preg_replace to suit PHP5.3 +
$data = preg_replace("/\n/", "\\n", $data);
if (isset($data)) {
$return.= '"' . $data . '"';
} else {
$return.= '""';
}
$return.= ',';
}
$return = substr($return, 0, strlen($return) - 1);
$return.= ");\n";
}
$return.="\n\n\n";
}
//save file
$handle = fopen($filepath, 'w+');
fwrite($handle, $return);
fclose($handle);
}
Cheers!
jc
maybe somebody can patch the code to work for InnoDB with foreign keys.
