How to use PHP to execute. sql file _php Tips

demo.php:

Copy Code code as follows:

<?php
/**
* Read the SQL file and write to the 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 (' # ', '--'))
{
To determine whether a file exists
if (!file_exists ($sqlPath))
return false;

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

$SQLSTR = Fread ($handle, FileSize ($sqlPath));

Split by statement delimiters in SQL syntax
$segment = Explode (";", Trim ($SQLSTR));

Var_dump ($segment);

get rid of annotations and extra blank lines
foreach ($segment as & $statement)
{
$sentence = Explode ("n", $statement);

$newStatement = Array ();

foreach ($sentence as $subSentence)
{
if ('!= trim ($subSentence))
{
Judge whether it will be a comment
$isComment = false;
foreach ($commenter as $comer)
{
if (eregi ("^ (" $comer.) ", Trim ($subSentence)))
{
$isComment = true;
Break
}
}
If it is not a comment, it is considered an SQL statement
if (! $isComment)
$newStatement [] = $subSentence;
}
}

$statement = $newStatement;
}
Prefix the table name
if ('!= $prefix)
{


Only table names are valid when the first row appears, such as CREATE table Talbename

$regxTable = "^[/'/"/"]{0,1}[/_a-za-z]+[/_a-za-z0-9]*[/'/"/"]{0,1}$";//The regular expression that handles the table name
$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]);
}

}
Combining SQL statements
foreach ($segment as & $statement)
{
$newStmt = ';
foreach ($statement as $sentence)
{
$newStmt = $newStmt. Trim ($sentence). /n ";
}

$statement = $newStmt;
}

For testing------------------------
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);
}
Test
$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
--
--Host: localhost
--Date Created: August 20, 2008 12:09
--Server version: 5.0.51
--PHP version: 5.2.5
SET sql_mode= "No_auto_value_on_zero";
--
--Database: ' Newysh '
--
-- --------------------------------------------------------
--
--The structure of the table ' 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;
--
--Export the data in the table ' allowed '
--
-- --------------------------------------------------------
--
--The structure of the table ' ALLOWED_EX '
--
CREATE TABLE ' allowed_ex ' (
' Bhash ' blob not NULL,
' badded ' datetime not NULL,
' Bsize ' bigint unsigned not NULL,
' Bfiles ' int (a) unsigned not NULL,
PRIMARY KEY (' Bhash ' (20))
) Engine=myisam DEFAULT charset=gb2312 row_format=dynamic;
--
--Export the data in the table ' ALLOWED_EX '
--
-- --------------------------------------------------------
--
--The structure of the table ' category '
--
CREATE TABLE ' category ' (
' CID ' int unsigned not NULL auto_increment COMMENT ' seed classification id ',
' Name ' varchar (255) Not NULL COMMENT ' category name, support HTML format ',
' Sequence ' int (a) unsigned not NULL COMMENT ' display sort, requiring a small row in front ',
PRIMARY KEY (' CID ')
) Engine=myisam DEFAULT Charset=utf8 auto_increment=26;
--
--Export the data in the table ' category '
--
INSERT into ' category ' (' cid ', ' name ', ' sequence ') VALUES
(25, ' music ', 23),
(24, ' learning materials ', 24),
(23, ' movie ', 25);
-----------------------------------------------------------
Note: For phpMyAdmin generated SQL files are applicable