Re: [PHP] mysql query and maximum characters in sql statement

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