How to insert text file to database table mysql

Question

I have a batchcode.txt which contains list of batch code.

and so on

I have a table in my database which is called batchcode with fields 'id','batchcode'.

My problem is it wont insert my text file into my table called batchcode. I already did the import and select table but nothing works...

I need some help can anyone help me please.

<?php
$host= "localhost";
$user= "root";
$pass= "";
$db="klayton";

$connect= mysql_connect($host,$user,$pass);
if (!$connect)die ("Cannot connect!");
mysql_select_db($db, $connect);

$file = fopen("batchcode.txt","r");  

while(! feof($file))
{
$sql = "INSERT INTO batchcode( batchcode ) VALUES ('fgets($file)')"; //Insert every read line from txt to mysql database
mysql_query($sql);
}
fclose($file);
?>

I got an error that says "Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp\htdocs\test\index.php on line 22"

How big is batchcode.txt in practice (megabytes or terabytes)? Do you have sysadmin access to the (Web & PHP) server? — Basile Starynkevitch
– Basile Starynkevitch, Commented Jan 26, 2014 at 14:19
The PHP script you've posted here does not contain 22 lines. Anyway, slightly related issue: the fgets() function will not be called the way it's currently written. You need to concatenate the value instead, i.e. $sql = "INSERT INTO batchcode( batchcode ) VALUES ('".fgets($file)."')"; - Note that this is still vulnerable to SQL injection though. You should stop using the deprecated mysql_* functions and switch to MySQLi / PDO (and start using prepared statements, so you're free from the risk of SQL injections). — Amal
– Amal, Commented Jan 26, 2014 at 14:21
@Nouphal.M: mysql_query() can only execute one query at most. — Amal
– Amal, Commented Jan 26, 2014 at 14:21
@Amal Murali What i mentioned was like this INSERT INTO tableName (fieldname) VALUES ('value1'),('value2')...('valuen'); — Nouphal.M
– Nouphal.M, Commented Jan 26, 2014 at 14:33

vidang · Accepted Answer · 2017-04-10 02:06:37Z

1

With your current code the function fgets($file) will never be executed, so ! feof($file) will always true, and the while loop keeps running until execution time limitation.

You should rewrite the insert query as following:

while( $batchcode = fgets($file) )
{
  $sql = "INSERT INTO batchcode( batchcode ) VALUES ('$batchcode')"; 
  mysql_query($sql);
}

edited Apr 10, 2017 at 2:06

answered Jan 26, 2014 at 14:48

vidang

1,78113 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3235574 Over a year ago

not working dude...it stores alot of fgets(Array) not the actual batchcode

aconrad · Accepted Answer · 2014-01-26 14:33:22Z

1

When php not in safe mode you can use max_execution_time

ini_set('max_execution_time', 0); //0 unlimited time, best to use a high number

Having one query per line is not a good ideea. Takes a long time as after every insert any indexes are rebuilt. You can use LOAD DATA LOCAL INFILE like:

LOAD DATA LOCAL INFILE 'C:\\bigfile.txt' INTO TABLE `database`.`batchcode` LINES TERMINATED BY '\r\n' (`batchcode `);

using \r\n or \n depends on your file. The path to the file must be where your mysql client runs.

answered Jan 26, 2014 at 14:33

aconrad

5866 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to insert text file to database table mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related