php sql输出,php 导出sql文件

//action中的代码

$sql = 'show tables';          //指定数据库所有的表

$connection=Yii::app()->db;

$command=$connection->createCommand($sql);

$queryTables = $command->query();

$tables = array();

foreach ($queryTables as $eachTable){

if(strpos($eachTable['Tables_in_'.Func::DB_NAME], 'view')===false){

array_push($tables, $eachTable['Tables_in_'.Func::DB_NAME]);

}

}

sort($tables);

//前台页面的显示

全选/全不选

下载sql文件

//导出的sql文

public function actionDownloadSql(){

//需要导出的表

$tables = $_GET['tables'];

$arrTables = explode(',', $tables);

$allSql = '';

foreach($arrTables as $table){

//创建表的查询

$sql = 'SHOW CREATETABLE '.$table;   //导出创建表的sql语句

$connection=Yii::app()->db;

$command=$connection->createCommand($sql);

$queryResult = $command->query();

foreach($queryResult as $each){

$createSql=str_replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS',$each['Create Table']);

}

//查询表的结构

$sql = 'DESCRIBE'.$table;    //查询表的结构

$connection=Yii::app()->db;

$command=$connection->createCommand($sql);

$queryColumn = $command->query();

$tableColumn = array();

foreach ($queryColumn as $each){

array_push($tableColumn, '`'.$each['Field'].'`');

}

//查询表内的数据

$sql = "select * from ".$table;

$connection=Yii::app()->db;

$command=$connection->createCommand($sql);

$queryResult = $command->query();

$tableData = array();

foreach($queryResult as $each){

$newEach = array();

foreach ($each as $val){

array_push($newEach, "'".$val."'");

}

array_push($tableData, "\n(".(implode(',', $newEach)).")");

}

$insertSql = " INSERT INTO `".$table."` (".(implode(",", $tableColumn)).") VALUES ".(implode(",",$tableData));

$allSql.= $createSql.";\n".$insertSql."\n";

}

header("Content-Type:text;");

header("Content-Disposition: p_w_upload; filename=usst".'.sql');

header("Pragma:no-cache");

header("Expires:0");

echo $allSql."\n";

exit;

}

难点：查询创建表的sql语句，查询表的结构，最后组装成sql文。

前台页面把数据库中所有的表显示出来，选择需要导出的表，然后进行导出。