PHP read TXT file and insert data into database, _php tutorial

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 ...

