It seems that THINKPHP does not have a way to back up the database, so I wrote one myself. The database connection and transaction processing are using pdo. If you need it, you can contact me and write a mysql or mysqli
The code is as follows:
class SqlAction extends Action{
function outsql(){
header(“Content-Type:text/html;charset=utf-8″);
/*Read database configuration using C method*/
$host=C(‘DB_HOST’);
$db_name=C(‘DB_NAME’);
$user=C(‘DB_USER’);
$password=C(‘DB_PWD’);
/*Call the private method of exporting the database*/
$outstream=$this->outputSql($host, $dbname, $user, $password);
/*Download export database*/
header(“Content-Disposition: attachment; filename=$dbname.sql”);
echo $outstream;
}
/*
* Database export function outputSql
* Export database data using PDO method
* $host host name such as localhost
* $dbname database name
* $user username
* $password password
* $flag flag bit 0 or 1. 0 means exporting only the database structure. 1 means exporting the database structure and data. The default is 1
*/
private function outputSql($host, $dbname, $user, $password, $flag=1) {
try {
$pdo = new PDO(“mysql:host=$host;dbname=$dbname”, $user, $password); //Connect to database
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Set tuning parameters and throw an exception when encountering an error
} catch (PDOException $e) {
echo $e->getMessage(); //If the connection is abnormal, an error message will be thrown
exit;
}
$mysql = "DROP DATABASE IF EXISTS `$dbname`;n"; //$mysql loads the sql statement. If a database exists here, drop the database
$creat_db=$pdo->query("show create database $dbname")->fetch();//Use show create database to view the sql statement
preg_match(‘/DEFAULT CHARACTER SET(.*)*/’, $creat_db[‘Create Database’],$matches);//Regularly remove the character set after DEFAULT CHARACTER SET
$mysql.=”CREATE DATABASE `$dbname` DEFAULT CHARACTER SET $matches[1]”;//This statement is such as CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8
/*Find the character sequence of the database such as COLLATE utf8_general_ci*/
$db_collate=$pdo->query(“SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME =’$dbname’ LIMIT 1″)->fetch();
$mysql.=”COLLATE “.$db_collate[‘DEFAULT_COLLATION_NAME’].”;nUSE `$dbname`;nn”;
$statments = $pdo->query(“show tables”); //Return the result set, show tables to view all table names
foreach ($statments as $value) {//Traverse this result set and export the information corresponding to each table name
$table_name = $value[0]; //Get the table name
$mysql.=”DROP TABLE IF EXISTS `$table_name`;n”; //Prepare Drop statements before each table
$table_query = $pdo->query(“show create table `$table_name`”); //Get the result set of the table creation information
$create_sql = $table_query->fetch(); //Use the fetch method to retrieve the array corresponding to the result set
$mysql.=$create_sql[‘Create Table’] . “;rnrn”; //Write table creation information
if ($flag != 0) {//If the flag is not 0, continue to retrieve the contents of the table and generate an insert statement
$iteams_query = $pdo->query(“select * from `$table_name`”); //Get the result set of all fields in the table
$values = ""; //Prepare empty string to load insert value
$items = ""; //Prepare empty string to load the table field name
while ($item_query = $iteams_query->fetch(PDO::FETCH_ASSOC)) { //Use associated query to return an array of field names and values in the table
$item_names = array_keys($item_query); //Get the array key value, that is, the field name
$item_names = array_map(“addslashes”, $item_names); //Translate special characters and add
$items = join(‘`,`’, $item_names); //Joint field names such as: items1`, `item2 `symbol is backticks. Next to keyboard 1, field names are enclosed in backticks
$item_values = array_values($item_query); //Get the array value, that is, the value corresponding to the field
$item_values = array_map(“addslashes”, $item_values); //Translate special characters and add
$value_string = join(“‘,’”, $item_values); //Joint values such as: value1′,'value2 values are enclosed in single quotes
$value_string = “(‘” . $value_string . “‘),”; //Add brackets around the value
$values.=”n” . $value_string; //Finally returned to $value
}
if ($values != “”) {//If $values is not empty, that is, the table has content
//Write insert statement
$insert_sql = “INSERT INTO `$table_name` (`$items`) VALUES” . rtrim($values, “,”) . “;nr”;
//Write this statement into $mysql
$mysql.=$insert_sql;
}
}
}
return $mysql;
}
}
?>
Isn’t it a very practical function? Friends can transplant it directly into their own projects.
