Php reads txt files and inserts data into the database

This article mainly introduces the methods and sample code for php to read txt files and insert data into the database. for small files, you can refer to the first method. for large files, see the second method. To test a function today, you need to insert some raw data into the database. PM gave a txt file. how to quickly split the contents of this txt file into the desired array, and then insert it into the database?

Sample content of serial_number.txt:

Serial_number.txt:

DM00001A11 0116,SN00002A11 0116,AB00003A11 0116,PV00004A11 0116,OC00005A11 0116,IX00006A11 0116,

Create a data table:

create table serial_number(id int primary key auto_increment not null,serial_number varchar(50) not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The php code is as follows:

$ Conn = mysql_connect ('2017. 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 ", "For the identifier to split foreach ($ 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 methods. here, after splitting the txt file into an array, we traverse the array produced by the Loop, insert each loop to the database.

I would like to share with you one that supports importing large files.

<? Php/*** $ splitChar field separator * $ file data file name * $ table database table name * $ conn database connection * $ column name corresponding to fields Data * $ insertType insert operation type, including INSERT, REPLACE */function loadtxtdata1_database ($ 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 = "$1 ','"; $ specialFunc = 'preg _ replace ';} else {$ splitChar = $ splitChar; $ replace = "', '"; $ specialFunc = 'Str _ replace ';} // process the data body. The order of the two parts cannot be changed. Otherwise, an error occurs when the space or Tab separator is used. $ sqldata = preg_replace ('/(\ s *) (\ n +) (\ s *) /I ',' \ '), (\ '', $ sqldata); // replace the line feed $ sqldata = $ specialFunc ($ splitChar, $ r Eplace, $ sqldata); // replace the separator $ query = $ head. $ sqldata. $ end; // data splicing 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 =' | '; // $ file = 'sqldata1.txt'; $ fields = array ('id', 'parentid', 'name '); $ table = 'cengji'; $ result = loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields); if (array_shift ($ re Sult) {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 (11) not null AUTO_INCREMENT, 'parentid' int (11) 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 = loadtxtdata1_database ($ 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 (11) not null AUTO_INCREMENT, 'make' varchar (16) not null, 'model' varchar (16) default null, 'year' varchar (16) 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 = 'xys'; $ insertType = 'replace'; $ result = loadtxtdata1_database ($ 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 3 require 'DB. php '; $ splitChar = ''; // Tab $ file = 'sqldata3.txt'; $ fields = array ('id', 'value'); $ table = 'notexist '; // no table exists $ result = loadtxtdata=database ($ splitChar, $ file, $ table, $ conn, $ fields); if (array_shift ($ result) {echo 'Success!
';} Else {echo' Failed! -- Error: '. array_shift ($ result ).'
';} // Appendix: db. php/* // comment this line to release it all?> <? 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 ;}?>

//*/

-- Data table structure:

-- 100000_insert, 1_00_insert

CREATE TABLE `100000_insert` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parentid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Insert 100000 (0.1 million) rows: Insert 100000_line_data use 2.5534288883209 seconds

Insert 1000000 (1 million) rows: Insert into 00_line_data use 19.677318811417 seconds

// Possible error: MySQL server has gone away

// Solution: modify my. ini/my. cnf max_allowed_packet = 20 M