Jim Lucas wrote:
> Sanjeev N wrote:
>> Hi,
>> I have written a program which imports the tab delimited file and
>> insert all
>> the line from file to the mysql line by line.
>> I am succeding in the above case. but problem with the above method is
>> its
>> taking to too much time while inserting into the database.
>> The file's size will be more than 5000 lines.
>>
>> Then i tried to build a string of ; seperated queries. as follows
>>
>> for($i=1; $i<sizeof($array); $i++){
>> $insert_string .= "insert into tablename (v1, v2..... v6)
>> values('$array[$i][1]', '$array[$i][2]'..... '$array[$i][6]');";
>> }
>> if(!empty($insert_string)){
>> mysql_query($insert_string, $conn) or die("query failed :
>> ".mysql_errror());
>> }
>>
>> Its throwing error saying check the manual for right syntax.....
>>
>> After investigating in some sites i come to know that its problem of
>> limitations in query size.
>>
>> I also tried with "SET GLOBAL max_allowed_packet=30000000;"
>> Then also its throwing the same error.
>>
>> Can anybody tell me how to fix this error and reduce the inserting
>> time with
>> a single statement instead of writing more insert statements
>>
>
> You are probably looking for something like this.
>
> <?php
>
> if ( count($array) ) {
> $insert_string = "INSERT INTO tablename (v1, v2..... v6) VALUES ";
> $data = array();
> foreach ( $array AS $row ){
> $row_clean = array_map('mysql_real_escape_string', $row);
> $data[] = "('{$row_clean[1]}',
> '{$row_clean[2]}',.....'{$row_clean[6]}')";
> }
> $insert_string = join(', ', $data);
> mysql_query($insert_string, $conn) or die("query failed :
> ".mysql_errror());
> } else {
> echo "Nothing to insert";
> }
>
> ?>
>

That would work, but will probably result in a query string that is too long.

I'll redo the above to fix that.


<?php

# How often do you want to insert??
$break_at = 100;

# Initialize the counter
$cnt = 0;

# Initial insert string
$insert_string = "INSERT INTO tablename (v1, v2..... v6) VALUES ";

# if there is data, then process, otherwise skip it.
if ( count($array) ) {

$data = array();

# Loop through data
foreach ( $array AS $row ) {

$cnt++;

# Clean the result data
$row_clean = array_map('mysql_real_escape_string', $row);

# Build data string and push it onto the data array.
$data[] = "('{$row_clean[1]}', '{$row_clean[2]}',.....'{$row_clean[6]}')";

# Break and insert if we are at the break point
if ( $cnt === $break_at ) {

# Reset Counter
$cnt = 0;

# Run insert
mysql_query($insert_string . join(', ', $data), $conn) or
die("query failed : ".mysql_error());

# Reset data array
$data = array();

} //if

} //foreach

# This should take care of any extra that didn't get processed in the foreach
if ( count($data) ) {

# Insert remaining data
mysql_query($insert_string . join(', ', $data), $conn) or
die("query failed : ".mysql_error());

} //if

} else {

echo "Nothing to insert";

} //if

?>


--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare