Fast updates mass data mysql from csv with php

Question

I'm trying to update my database with php. unfortunately the process is far too long. I can't use a data load infile because my hosting doesn't allow it.

My database rows ( id, sku, stock, image_1, image_2, image_3) my csv file ( sku, stock)

i try to update only the stock from csv file into my database.

<?php      
    $connect = mysqli_connect("localhost", "databsename", "PAssword", "databsename");
    $handle = fopen("new_stock.csv", "r");
    while($data = fgetcsv($handle))
    {
        $sku = mysqli_real_escape_string($connect, $data[0]); 
        $stock = mysqli_real_escape_string($connect, $data[1]);   
        $query = "UPDATE Products SET stock='$stock' WHERE sku='$sku'";
        mysqli_query($connect, $query);
    }
    fclose($handle);
    echo 'finish';
?>

(Possible) side note: Forget about `*escape_string*()` and do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make (still leave) your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Aug 10 '21 at 12:42
`is far too long` means you hit execution time limit or what? — user3783243, Aug 10 '21 at 12:46
With respect I disagree with the decision to close this question for lack of details. It presents the questioner's problem quite clearly. — O. Jones, Aug 10 '21 at 13:26

score 0 · Answer 1 · edited Aug 22 '22 at 19:45

0

Maybe with the following code, you can do that. I had the same problem.

set_time_limit(0);
$connect = mysqli_connect("localhost", "databsename", "PAssword", "databsename");
$handle = fopen("new_stock.csv", "r");
$str = [];
$flag = true;
$counter = 0;
$str2 = "";
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {

    if ($flag) {
        $flag = false;
        continue;
    };
    
    $sku = mysqli_real_escape_string($connect, $data[0]); 
    $stock = mysqli_real_escape_string($connect, $data[1]);   
    $query = "UPDATE Products SET stock='$stock' WHERE sku='$sku'; ";

   
    $counter++;
    $str2 .= $query;
    if ($counter % 10000 == 0) {
        $str[] = $str2;
        $str2 = "";
    }
}

fclose($handle);
if ($str2 != "")
    $str[] = $str2;
    

foreach ($str as $item) {
    $item2 = str_replace('\"', '"', $item2);
    mysqli_query($connect, $item);
}
echo 'finish';

edited Aug 22 '22 at 19:45

M.Sarabi

51
1
9

answered Aug 10 '21 at 12:55

Sohrab

91
1
9

then the script runs for several hours right? – Robin focke Aug 10 '21 at 13:25
No. Why several hours? It will done as its work ends. And it will be pretty fast I guess. – Sohrab Aug 10 '21 at 13:28
is this correct ? $handle = fopen("new_stock.csv", "r"); $file = fopen($handle); – Robin focke Aug 10 '21 at 15:24
You right. sorry. I edited my answer. – Sohrab Aug 10 '21 at 15:36
And $connect must be at the top ? – Robin focke Aug 10 '21 at 15:57
bro your code not work – Robin focke Aug 14 '21 at 21:24

O. Jones · Answer 2 · 2021-08-10T14:19:19.317

0

The trick here is to place multiple UPDATE operations in a single transaction. Why? MySQL does the bulk of its UPDATE and INSERT work when you COMMIT your work. Therefore, fewer transactions lead to faster operations. Ordinarily mysqli uses "autocommit" mode, meaning each statement is its own transaction. But here we use mysqli_begin_transaction() and mysqli_commit().

Something like this will work (not! debugged). It also uses a prepared statement and bind variables in place of the deprecated mysqli_real_escape_string.

    $transactionSize = 500;
    $handle = fopen("new_stock.csv", "r");
    $countdown = $transactionSize;
    mysqli_begin_transaction($connection);
    $updateStatement = mysqli_prepare($connection, 
                        "UPDATE Products SET stock=? WHERE sku=?");
    while($data = fgetcsv($handle)) {
        set_time_limit(30);
        mysqli_stmt_bind_param($updateStatement , "ss", $data[0], $data[1]);
        mysqli_stmt_execute($updateStatement);
        $countdown--;
        if ($countdown <= 0) {
           /* end one transaction, start another */
           mysqli_commit($connection);
           mysqli_begin_transaction($connection);
           $countdown = $transactionSize
        }
    }
    mysqli_commit($connection);
    fclose($handle);
    echo 'finish';
?>

This code pattern, or something similar, is useful for all sorts of multirow INSERTs and UPDATEs.

edited Aug 10 '21 at 14:19

answered Aug 10 '21 at 13:03

O. Jones

103,626
17
118
172

then the script runs for several hours right? – Robin focke Aug 10 '21 at 13:23
With code like mine it should run somewhere between 10 and 100 times faster than it would with your sample code. – O. Jones Aug 10 '21 at 13:25
Interesting code! Really pro! – Sohrab Aug 10 '21 at 13:28
Thanks for the kind words. I've done this kind of thing too many times :-) – O. Jones Aug 10 '21 at 14:18
the script takes a very long time with 350,000 data records – Robin focke Aug 10 '21 at 15:44
Gateway Timeout The gateway did not receive a timely response from the upstream server or application – Robin focke Aug 10 '21 at 15:45

Fast updates mass data mysql from csv with php

2 Answers2