php 导出sql文件

原创

wanfeng1979 2012-05-23 17:31:44 ©著作权

©著作权归作者所有：来自51CTO博客作者wanfeng1979的原创作品，请联系作者获取转载授权，否则将追究法律责任

//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);
//前台页面的显示
<a href="javascript:void(0);" id="selectAll" >全选</a>/<a href="javascript:void(0);" id="selectNotAll">全不选</a><br/>
        <select id="selectTables" size="5" multiple style="height:180px!important; width:250px;">
        <?php foreach ($tables as $eachTable){?>
        <option value="<?php echo $eachTable;?>" ><?php echo $eachTable;?></option>
        <?php }?>
        </select>
        <br/>
        <a class="button" target="_blank"><span class="icon icon-output">下载sql文件</span></a>

        <script type="text/javascript">
        
        </script>
//导出的sql文
public function actionDownloadSql(){
       //需要导出的表
       $tables = $_GET['tables'];
       $arrTables = explode(',', $tables);
       $allSql = '';
       foreach($arrTables as $table){
           //创建表的查询
            $sql = 'SHOW CREATE TABLE '.$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;
   }