Wouldn't using LOAD DATA INFILE be better than writing your own script?
On 5/1/08, Jim Lucas <lists@xxxxxxxxx> wrote:
> 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
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
