Php reads txt files and inserts data into the database

: This article mainly introduces php to read txt files and insert data into the database. For more information about PHP tutorials, see. 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"); $ c // 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

The above introduces php to read txt files and insert data into the database, including some content, hope to be helpful to friends who are interested in PHP tutorials.