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

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