您当前的位置: 首页 > 网站编程 > PHP教程 > 如何利用PHP执行.SQL文件

如何利用PHP执行.SQL文件

作者:不详 来源:网络 发布时间: 2014-08-16 10:22 点击:
本篇文章是对使用PHP执行.SQL文件的实现代码进行了详细的分析介绍,需要的朋友参考下 demo.php: 复制代码 代码如下: ?php /** * 读取 sql 文件并写入数据库 * @version 1.01 demo.php */ class DBManager { var $dbHost = ''; var $dbUser = ''; var $dbPassword = '';

如何利用PHP执行.SQL文件

  本篇文章是对使用PHP执行.SQL文件的实现代码进行了详细的分析介绍,需要的朋友参考下

  demo.php:

  


  复制代码 代码如下:

  <?php

  /**

  * 读取 sql 文件并写入数据库

  * @version 1.01 demo.php

  */

  class DBManager

  {

     var $dbHost = '';

     var $dbUser = '';

     var $dbPassword = '';

     var $dbSchema = '';

     function __construct($host,$user,$password,$schema)

     {

         $this->dbHost = $host;

         $this->dbUser = $user;

         $this->dbPassword = $password;

         $this->dbSchema = $schema;

     }

     function createFromFile($sqlPath,$delimiter = '(;/n)|((;/r/n))|(;/r)',$prefix = '',$commenter = array('#','--'))

     {

         //判断文件是否存在

         if(!file_exists($sqlPath))

             return false;

         $handle = fopen($sqlPath,'rb'); 

         $sqlStr = fread($handle,filesize($sqlPath));

         //通过sql语法的语句分割符进行分割

         $segment = explode(";",trim($sqlStr));

         //var_dump($segment);

         //去掉注释和多余的空行

         foreach($segment as & $statement)

         {

             $sentence = explode("/n",$statement);

             $newStatement = array();

             foreach($sentence as $subSentence)

             {

                 if('' != trim($subSentence))

                 {

                     //判断是会否是注释

                     $isComment = false;

                     foreach($commenter as $comer)

                     {

                         if(eregi("^(".$comer.")",trim($subSentence)))

                         {

                             $isComment = true;

                             break;

                         }

                     }

                     //如果不是注释,则认为是sql语句

                     if(!$isComment)

                         $newStatement[] = $subSentence;                 

                 }

             }

             $statement = $newStatement;

         }

         //对表名加前缀

         if('' != $prefix)

         {

       

             //只有表名在第一行出现时才有效 例如 CREATE TABLE talbeName

             $regxTable = "^[/`/'/"]{0,1}[/_a-zA-Z]+[/_a-zA-Z0-9]*[/`/'/"]{0,1}$";//处理表名的正则表达式

             $regxLeftWall = "^[/`/'/"]{1}";

             $sqlFlagTree = array(

                     "CREATE" => array(

                             "TABLE" => array(

                                     "$regxTable" => 0

                                 )

                         ),

                     "INSERT" => array(

                             "INTO" => array(

                                 "$regxTable" => 0

                             )

                         )

                     );

             foreach($segment as & $statement)

             {

                 $tokens = split(" ",$statement[0]);

                 $tableName = array();

                 $this->findTableName($sqlFlagTree,$tokens,0,$tableName);

                 if(empty($tableName['leftWall']))

                 {

                     $newTableName = $prefix.$tableName['name'];

                 }

                 else{

                     $newTableName = $tableName['leftWall'].$prefix.substr($tableName['name'],1);

                 }

                 $statement[0] = str_replace($tableName['name'],$newTableName,$statement[0]);

             }

         }     

         //组合sql语句

         foreach($segment as & $statement)

         {

             $newStmt = '';

             foreach($statement as $sentence)

             {

                 $newStmt = $newStmt.trim($sentence)."/n";

             }

             $statement = $newStmt;

         }

         //用于测试------------------------     

         //var_dump($segment);

         //writeArrayToFile('data.txt',$segment);

         //-------------------------------

         self::saveByQuery($segment);

         return true;

     }

     private function saveByQuery($sqlArray)

     {

         $conn = mysql_connect($this->dbHost,$this->dbUser,$this->dbPassword);

         mysql_select_db($this->dbSchema);

         foreach($sqlArray as $sql)

         {

             mysql_query($sql);

         }     

         mysql_close($conn);

     }

     private function findTableName($sqlFlagTree,$tokens,$tokensKey=0,& $tableName = array())

     {

         $regxLeftWall = "^[/`/'/"]{1}";

         if(count($tokens)<=$tokensKey)

             return false;     

         if('' == trim($tokens[$tokensKey]))

         {

             return self::findTableName($sqlFlagTree,$tokens,$tokensKey+1,$tableName);

         }

         else

         {

             foreach($sqlFlagTree as $flag => $v)

             { 

                 if(eregi($flag,$tokens[$tokensKey]))

                 {

                     if(0==$v)

                     {

                         $tableName['name'] = $tokens[$tokensKey];

                         if(eregi($regxLeftWall,$tableName['name']))

                         {

                             $tableName['leftWall'] = $tableName['name']{0};

                         }

                         return true;

                     }

                     else{

                         return self::findTableName($v,$tokens,$tokensKey+1,& $tableName);

                     }

                 }

             }

         }

         return false;

     }

  }

  function writeArrayToFile($fileName,$dataArray,$delimiter="/r/n")

  {

     $handle=fopen($fileName, "wb");

     $text = '';

     foreach($dataArray as $data)

     {

         $text = $text.$data.$delimiter;

     }

     fwrite($handle,$text);

  }

  //测试

  $dbM = new DBManager('localhost','w01f','123456','test');

  $dbM->createFromFile('data.sql',null,'fff_');

  ?>

  data.sql:

  
-- phpMyAdmin SQL Dump

  -- version 2.11.3

  -- http://www.phpmyadmin.net

  --

  -- 主机: localhost

  -- 生成日期: 2008 年 08 月 20 日 12:09

  -- 服务器版本: 5.0.51

  -- PHP 版本: 5.2.5

  SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

  --

  -- 数据库: `newysh`

  --

  -- --------------------------------------------------------

  --

  -- 表的结构 `allowed`

  --

  CREATE TABLE `allowed` (

  `bhash` blob NOT NULL,

  `bname` varchar(255) character set utf8 NOT NULL,

  PRIMARY KEY (`bhash`(20))

  ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 ROW_FORMAT=DYNAMIC;

  --

  -- 导出表中的数据 `allowed`

  --

  -- --------------------------------------------------------

  --

  -- 表的结构 `allowed_ex`

  --

  CREATE TABLE `allowed_ex` (

  `bhash` blob NOT NULL,

  `badded` datetime NOT NULL,

  `bsize` bigint(20) unsigned NOT NULL,

  `bfiles` int(10) unsigned NOT NULL,

  PRIMARY KEY (`bhash`(20))

  ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 ROW_FORMAT=DYNAMIC;

  --

  -- 导出表中的数据 `allowed_ex`

  --

  -- --------------------------------------------------------

  --

  -- 表的结构 `category`

  --

  CREATE TABLE `category` (

  `cid` int(10) unsigned NOT NULL auto_increment COMMENT '种子分类id',

  `name` varchar(255) NOT NULL COMMENT '分类名称,支持html格式',

  `sequence` int(10) unsigned NOT NULL COMMENT '显示排序,需要小的排在前面',

  PRIMARY KEY (`cid`)

  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;

  --

  -- 导出表中的数据 `category`

  --

  INSERT INTO `category` (`cid`, `name`, `sequence`) VALUES

  (25, '音乐', 23),

  (24, '学习资料', 24),

  (23, '电影', 25);

  -----------------------------------------------------------

  注:对于phpmyadmin 生成的sql文件均适用
分享到:
本文"如何利用PHP执行.SQL文件"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4