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