Php reads a txt file to form an SQL statement and inserts it into the database

Php reads a txt file to form an SQL statement and inserts it into the database

/** * $ SplitChar field delimiter * $ File data file name * $ Table database table name * $ Conn database connection * $ Name of the column corresponding to fields data * $ InsertType INSERT operation type, including INSERT and REPLACE * Collect: bbs.it-home.org */ 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 line feed $ Sqldata = $ specialFunc ($ splitChar, $ replace, $ sqldata); // replace the 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 1 Require 'DB. php '; $ SplitChar = '|'; // vertical bar $ File = 'sqldata1.txt '; $ Fields = array ('id', 'parentid', 'name '); $ Table = 'cengji '; $ Result = loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields ); If (array_shift ($ result )){ Echo 'Success! '; } Else { Echo 'failed! -- Error: '. array_shift ($ result ).' '; } /* Sqlda ta1.txt | 0 | | 1 | B | 1 | C | 2 | D -- Cengji Create 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.txt Aston DB19 2009 Aston DB29 2009 Aston DB39 2009 -- Cars Create 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 = 'Cars '; $ 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.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'; // The table does not exist. $ Result = loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields ); If (array_shift ($ result )){ Echo 'Success! '; } Else { Echo 'failed! -- Error: '. array_shift ($ result ).' '; } // Appendix: db. php /* // Note that all rows can be released. ?> 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; } ?> The code is as follows: //*/ Data table structure -- Data table structure: -- 100000_insert, 1_00_insert Create table '2017 _ 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 Author: Zjmainstay