How to use PHP to execute. SQL files

This article provides a detailed analysis of the implementation code of PHP execution. SQL file. For more information, see Demo. php:

The code is as follows:

/**
* Read the SQL file and write it 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)', $ prefix = '', $ commenter = array ('#','--'))
{
// Determine whether a file exists
If (! File_exists ($ sqlPath ))
Return false;

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

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

// Use the statement delimiter of SQL syntax to separate
$ Segment = explode (";", trim ($ sqlStr ));

// Var_dump ($ segment );

// Remove comments and extra blank lines
Foreach ($ segment as & $ statement)
{
$ Sentence = explode ("/n", $ statement );

$ NewStatement = array ();

Foreach ($ sentence as $ subSentence)
{
If (''! = Trim ($ subSentence ))
{
// Determine whether it is 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;
}
// Add a prefix to the table name
If (''! = $ Prefix)
{


// Valid only when the TABLE name appears in the first row, for example, create table talbeName

$ RegxTable = "^ [/'/"] {0, 1} [/_ a-zA-Z] + [/_ a-zA-Z0-9] * [/'/"] {0, 1} $ "; // process the regular expression of 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]);
}

}
// Combine SQL statements
Foreach ($ segment as & $ statement)
{
$ NewStmt = '';
Foreach ($ statement as $ sentence)
{
$ NewStmt = $ newStmt. trim ($ sentence). "/n ";
}

$ Statement = $ newStmt;
}

// Used 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', '000000', 'test ');
$ DbM-> createFromFile ('data. SQL ', null, 'fff _');
?>

Data. SQL:
-- PhpMyAdmin SQL Dump
-- Version 2.11.3
Http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation date: August 20, 2008
-- Server version: 5.0.51
-- PHP version: 5.2.5
SET SQL _MODE = "NO_AUTO_VALUE_ON_ZERO ";
--
-- Database: 'newsh'
--
----------------------------------------------------------
--
-- Table structure '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 'allowed' in the table'
--
----------------------------------------------------------
--
-- Table structure '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;
--
-- Export the table data 'allowed_ex'
--
----------------------------------------------------------
--
-- Table structure 'category'
--
Create table 'Category '(
'CID' int (10) unsigned not null auto_increment COMMENT 'seed category ID ',
'Name' varchar (255) not null comment' category name, html format supported ',
'Sequence 'Int (10) unsigned not null comment': displays the order, and requires a small number of headers ',
Primary key ('CID ')
) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 26;
--
-- Export the data 'Category' in the table'
--
Insert into 'category '('CID', 'name', 'sequence') VALUES
(25, 'Music', 23 ),
(24, 'learning information', 24 ),
(23, 'Cine ', 25 );
-----------------------------------------------------------
Note: Applicable to SQL files generated by phpmyadmin