Home > Backend Development > PHP Tutorial > How to use PHP to execute .SQL files_PHP Tutorial

How to use PHP to execute .SQL files_PHP Tutorial

WBOY
Release: 2016-07-21 15:00:18
Original
989 people have browsed it

demo.php:

Copy code The code is as follows:

/**
* Read sql file and write to database
* @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 ('#','--'))
{
//Determine whether the file exists
if(!file_exists($sqlPath))
return false;

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

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

//Split by the statement separator of sql syntax
       $segment = explode(";",trim($sqlStr));

             //var_dump($segment);                                                                                                                                               foreach($segment as & $statement)
                                          $sentence =                                                 ($ Sentence as $ Subsentence)
{
if (''! = Trim ($ Subsentence))
{
// Determine whether it will be notes
$ isComment = false;
foreach($commenter as $comer)
{
🎜>                                                                                                                             true;
break;
}
}
//If not Comment, it is considered to be a sql statement
                                                                                                                                                                                          🎜>                                                                                                                                   
                                                                                                   TABLE talbeName

                           $regxTable = "^[/`/'/"]{0,1}[/_a-zA-Z]+[/_a-zA-Z0-9]*[/`/ '/"]{0,1}$";//Regular expression for processing table names
                                                                                           $regxLeftWall =                                               = array(
“CREATE” => array(
“TABLE” => array(
) "$regxTable" => 0
                                                                                                              " => array(
"INTO" "INTO" "$regxTable" => array(
"$regxTable" => 0
)
)
)

);

foreach($segment as & $statement)
                                                                                       $this- >findTableName($sqlFlagTree,$tokens,0,$tableName);

                                                                 Name = $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文件均适用

www.bkjia.comtruehttp://www.bkjia.com/PHPjc/328062.htmlTechArticledemo.php: 复制代码 代码如下: ?php /*** Read sql file and write to database * @version 1.01 demo.php*/ class DBManager { var $dbHost = ''; var $dbUser = ''; var $dbPasswor...
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template