CODEWITHSUNDEEP
phpmysqlsql
adi
adi

Reputation: 191

Upload data to mysql with php

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);
?>

Upvotes: 3

Views: 85

Answers (3)

Ari Seyhun
Ari Seyhun

Reputation: 12511

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;
}

Upvotes: 0

Jagrati
Jagrati

Reputation: 12222

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.

Upvotes: 3

Nitya Kumar
Nitya Kumar

Reputation: 968

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

Upvotes: 0

Related Questions