Upload data to mysql with php

Question

I try to upload my data to mysql Its working but it takes 35sec, too many sec.

What do I need to change in my code that it will work faster than 35 sec?

I use php to write my code and SQL query to send the data to my table that called "words" .

At my table in the database I have 4 columns ('word', 'num', 'hit', 'instoplist').

What I can do to fix this problem?

Thanks

This is my code:

<?php
    function removeStopWordsFromArray($words)
    {     
        ....... 
        insert($words);
    }

    function insert($myWords)
    {
        global $conn;

        foreach ($myWords as $key => $value) {
            $word = $value['word'];
            $number = $value['document'];
            $hit = $value['hit'];
            $stop = $value['stopList'];

             $sql = "INSERT INTO words (word,num,hit,instoplist) VALUES ('$word', '$number', '$hit','$stop')";

             if($conn->query($sql)!== TRUE)
             {
                    echo "error". $conn->error;
             }
        }

        fclose($fp);
    }

    $temp = pareseDocs();
    removeStopWordsFromArray($temp);
?>

is this something you are doing regularly? If not just forget it — e4c5, Sep 15 '16 at 13:10
Collect the values via `$values[]= "('$word', '$number', '$hit','$stop');"` then write them togheter like `$sql = "INSERT INTO words (word,num,hit,instoplist) VALUES ".implode(', ',$values);` if you have too many values use `foreach(array_splice($values,50) as $subset){/*same sql as before*/ }` — JustOnUnderMillions, Sep 15 '16 at 13:12
And if that not helps, then update your question with more info. Because from where does `$fp` in `fclose($fp);` come? Half copied source? — JustOnUnderMillions, Sep 15 '16 at 13:14
Try to do only one big query instead one query per iteration, as people say. Concatenate strings instead variable replacement can also be helpful. http://stackoverflow.com/questions/13620/speed-difference-in-using-inline-strings-vs-concatenation-in-php5 — Alexi, Sep 15 '16 at 13:23
Why does nobody scream _USE MYSQLi instead of MYSQL_ and _USE PREPARED STATMENTS HERE_ !!!! — JustOnUnderMillions, Sep 15 '16 at 13:26
what i mean is that if this is a one off task, why spent 40 minutes to save 35 seconds? — e4c5, Sep 15 '16 at 13:48

score 3 · Accepted Answer · answered Sep 15 '16 at 13:14

For every data you are running a query in DB. But the correct way in your case is to insert data in batches. You can write the code is following way:

    $sql = "INSERT INTO words (word,num,hit,instoplist) VALUES";
     foreach ($myWords as $key => $value) {
                $word = $value['word'];
                $number = $value['document'];
                $hit = $value['hit'];
                $stop = $value['stopList'];        
                $sql .= "('$word', '$number', '$hit','$stop'),";                 
            }
            $sql = rtrim($sql,',') //to remove last comma


            if($conn->query($sql)!== TRUE)
            {
                        echo "error". $conn->error;
            }

This will run only single query in DB. Hence will be faster.

See my comment above and notice `$sql.= + rtrim()` vs. `$val[]= + implode()` and the possiblity too use `array_splice()` if you have too much data!! ;) — JustOnUnderMillions, Sep 15 '16 at 13:22

score 0 · Answer 2 · answered Sep 15 '16 at 13:13

0

You can try this query outside of loop pass only one query:

INSERT IGNORE INTO MyTable ( Column1, Column2 ) VALUES
    ( Value1, Value2 ), ( Value1, Value2 )

IGNORE FOR HANDLING ERRORS

answered Sep 15 '16 at 13:13

Nitya Kumar

967
8
14

score 0 · Answer 3 · edited May 23 '17 at 11:45

Your problem is that you are making each query separately in a for loop.

Take a look at https://stackoverflow.com/a/452934/4988637 to find out more on how to insert mutliple rows in a single query.

If you change your method to one single query, you should find your program's run-time to be drastically shortened.

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement.
INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )
For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008.

In your case, you could modify your code to look something like the following.

$sql = "INSERT INTO words (word, num, hit, instoplist) VALUES ";
foreach($myWords as $key => $value) {
    $word = $value['word'];
    $number = $value['document'];
    $hit = $value['hit'];
    $stop = $value['stopList'];        
    $sql .= "('$word', '$number', '$hit','$stop'),";                 
}
$sql = rtrim($sql, ',');

if($conn->query($sql) !== true) {
    echo "error".$conn->error;
}

Upload data to mysql with php

3 Answers3

Your problem is that you are making each query separately in a for loop.