PHP reads the TXT file and inserts the data into the database.
Today testing a function, you need to insert some raw data into the database, PM to a txt file, how to quickly split the content of the TXT file into the desired array, and then inserted into the database?
Sample content for Serial_number.txt:
Serial_number.txt:
Dm00001a11 0116,sn00002a11 0116,ab00003a11 0116,pv00004a11 0116,oc00005a11 0116,ix00006a11 0116,
To create a data table:
CREATE table Serial_number (ID int primary KEY auto_increment not null,serial_number varchar () NOT NULL) Engine=innodb DEF Ault Charset=utf8;
The PHP code is as follows:
$conn = mysql_connect (' 127.0.0.1 ', ' root ', ') or Die ("Invalid query:". Mysql_error ()); mysql_select_db (' Test ', $conn) or Die ("Invalid query:". Mysql_error ()); $content = file_get_contents ("Serial_number.txt"), $contents = Explode (",", $content);//explode () function with " , "Split foreach for Identifiers ($contents as $k + = $v)//traversal loop { $id = $k; $serial _number = $v; mysql_query ("INSERT into serial_number (' id ', ' serial_number ') VALUES (' $id ', ' $serial _number ')");}
Note: There are many ways to do this, I am here to split the TXT file into an array, and then iterate through the resulting arrays, once per loop, into the database once.
To share with you a support for large file import
<?php/** * $splitChar Field delimiter * $file data file filename * $table database table name * $conn Database connection * $fields data corresponding column name * $insertType insert operation type, including Inser T,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 header $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, which is not available in order, otherwise the space or tab delimiter error $sqldata = Preg_replace ('/(\s*) (\n+) (\s*)/i ', ' \ '), (\ ' ', $sqldata); Replace newline $sqldata = $specialFunc ($splitChar, $replace, $sqldata); Replace delimiter $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 1require ' db.php '; $splitChar = ' | '; Vertical bar $file = ' sqldata1.txt '; $fields = array (' id ', ' parentid ', ' name '); $table = ' Cengji '; $result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields); if (Array_shift ($result)) {echo ' success!
';} else {echo ' Failed!--Error: '. Array_shift ($result). '
';} /*sqlda ta1.txt1|0| a2|1| b3|1| c4|2| d--cengjicreate TABLE ' Cengji ' (' id ' int (one) not null auto_increment, ' parentid ' int (one) not null, ' name ' varchar (255) D Efault NULL, PRIMARY key (' id '), UNIQUE key ' Parentid_name_unique ' (' ParentID ', ' name ') USING BTREE) Engine=innodb auto_inc rement=1602 DEFAULT charset=utf8*///Call Example 2require ' 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!
';} else {echo ' Failed!--Error: '. Array_shift ($result). '
';} /* sqldata2.txt11 Aston DB19 200912 Aston DB29 200913 Aston DB39 2009--carscreate TABLE ' cars ' (' id ' int ') not NULL AU To_increment, ' make ' varchar (+) NOT NULL, ' model ' varchar (+) default NULL, ' year ' varchar (+) default NULL, PRIMARY KEY (' ID ')) Engine=innodb auto_increment=14 DEFAULT charset=utf8*///Call Example 3require ' 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!
';} else {echo ' Failed!--Error: '. Array_shift ($result). '
';} /* sqldata3.txt11 Aston DB19 200912 Aston DB29 200913 Aston DB39 2009 *///Call Example 3require ' db.php '; $splitChar = ' ; Tab$file = ' Sqldata3.txt '; $fields = array (' ID ', ' value '); $table = ' notexist '; Non-existent table $result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields); if (Array_shift ($result)) {echo ' success!
';} else {echo ' Failed!--Error: '. Array_shift ($result). '
';} Attached: db.php/*//Note this line can be completely released? ><?phpstatic $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;}? >
//*/
Copy Code
--Data table structure:
--100000_insert,1000000_insert
CREATE TABLE ' 100000_insert ' (' id ' int (one) not null auto_increment, ' parentid ' int (one) not null, ' name ' varchar (255) DEFA ULT NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=1 DEFAULT Charset=utf8
100000 (100,000) row insert: Insert 100000_line_data use 2.5534288883209 seconds
1000000 (1 million) row insert: Insert 1000000_line_data use 19.677318811417 seconds
Possible error: MySQL server has gone away
FIX: Modify MY.INI/MY.CNF max_allowed_packet=20m
Articles you may be interested in:
- PHP read txt file to compose SQL and insert database code (original from Zjmainstay)
- A method of importing large amounts of data into a database based on PHP read TXT file
http://www.bkjia.com/PHPjc/1102466.html www.bkjia.com true http://www.bkjia.com/PHPjc/1102466.html techarticle PHP read txt file and insert data into the database, test a function today, need to insert some raw data into the database, PM to a txt file, how to quickly put this txt text ...