adi adi - 2 months ago 9
MySQL Question

upload data to mysql with php

I try to upload my data to mysql Its working but its take 35sec, to many sec.
What I need to change in my code that it will work faster then 35 sec.
I use php to write my code and sql qurey to send the data to my table that called "words" .
At my table in the database I have 4 col ('word','num','hit','instoplist').

What I can do to fiex this problem?
Thank's

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

Answer

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.