/**
* $splitChar Field Separator
* $file data file filename
* $table database table name
* $conn Database connection
* $fields the corresponding column name of the data
* $insertType insert operation type, including Insert,replace
*/
Copy Code code as follows:
<?php
/**
* $splitChar Field Separator
* $file data file filename
* $table database table name
* $conn Database connection
* $fields the corresponding column name of the data
* $insertType insert operation type, including Insert,replace
*/
function Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields =array (), $insertType = ' INSERT ') {
if (empty ($fields)) $head = "{$insertType} into ' {$table} ' VALUES (';
else $head = ' {$insertType} into ' {$table} '. Implode (', ', $fields). " ') VALUES (' "; Data headers
$end = "')";
$sqldata = Trim (file_get_contents ($file));
if (preg_replace ('/\s*/i ', ', ', $splitChar) = = ") {
$splitChar = '/(\w+) (\s+)/I ';
$replace = "$ ', '";
$specialFunc = ' preg_replace ';
}else {
$splitChar = $splitChar;
$replace = "', '";
$specialFunc = ' str_replace ';
}
Processing the data body, the order of the two is not changed, otherwise the Space or tab separator error
$sqldata = Preg_replace ('/(\s*) (\n+) (\s*)/i ', ' \ '), (\ ', $sqldata); Replace line Wrap
$sqldata = $specialFunc ($splitChar, $replace, $sqldata); Replace separator
$query = $head. $sqldata. $end; Data stitching
if (mysql_query ($query, $conn)) return array (true);
else {
Return Array (False,mysql_error ($conn), Mysql_errno ($conn));
}
}
Call Example 1
Require ' db.php ';
$splitChar = ' | '; Vertical
$file = ' sqldata1.txt ';
$fields = array (' id ', ' parentid ', ' name ');
$table = ' Cengji ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!<br/> ';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). ' <br/> ';
}
/*sqlda Ta1.txt
|0| A
|1| B
|1| C
|2| D
--Cengji
CREATE TABLE ' Cengji ' (
' id ' int (one) not NULL auto_increment,
' ParentID ' int (one) is not NULL,
' Name ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' parentid_name_unique ' (' ParentID ', ' name ') USING btree
) Engine=innodb auto_increment=1602 DEFAULT Charset=utf8
*/
Call Example 2
Require ' db.php ';
$splitChar = '; Space
$file = ' sqldata2.txt ';
$fields = array (' id ', ' make ', ' model ', ' year ');
$table = ' cars ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!<br/> ';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). ' <br/> ';
}
/* Sqldata2.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
--Cars
CREATE TABLE ' Cars ' (
' id ' int (one) not NULL auto_increment,
' Make ' varchar is not NULL,
' model ' varchar DEFAULT NULL,
' Year ' varchar DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=14 DEFAULT Charset=utf8
*/
Call Example 3
Require ' db.php ';
$splitChar = '; Tab
$file = ' sqldata3.txt ';
$fields = array (' id ', ' make ', ' model ', ' year ');
$table = ' cars ';
$insertType = ' REPLACE ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields, $insertType);
if (Array_shift ($result)) {
Echo ' success!<br/> ';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). ' <br/> ';
}
/* Sqldata3.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
*/
Call Example 3
Require ' db.php ';
$splitChar = '; Tab
$file = ' sqldata3.txt ';
$fields = array (' ID ', ' value ');
$table = ' notexist '; No table exists
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!<br/> ';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). ' <br/> ';
}
Attached: db.php
///Note this line can be completely released
?>
<?php
static $connect = null;
static $table = ' Jilian ';
if (!isset ($connect)) {
$connect = mysql_connect ("localhost", "root", "");
if (! $connect) {
$connect = mysql_connect ("localhost", "Zjmainstay", "");
}
if (! $connect) {
Die (' Can not connect to database. Fatal error handle by/test/db.php ');
}
mysql_select_db ("Test", $connect);
mysql_query ("SET NAMES UTF8", $connect);
$conn = & $connect;
$db = & $connect;
}
?>
//*/
Data table structure
Copy Code code as follows:
--Data table structure:
--100000_insert,1000000_insert
CREATE TABLE ' 100000_insert ' (
' id ' int (one) not NULL auto_increment,
' ParentID ' int (one) is not NULL,
' Name ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=1 DEFAULT Charset=utf8
100000 (100,000) line insert: Insert 100000_line_data use 2.5534288883209 seconds
1000000 (1 million) line insert: Insert 1000000_line_data use 19.677318811417 seconds
Possible error: MySQL server has gone away
Workaround: Modify MY.INI/MY.CNF max_allowed_packet=20m
Author: Zjmainstay
