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";
}

?>

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