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

Source: Internet
Author: User

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

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.