WordPress WPDB – insert/update multiple rows at once

I am reading a CSV with 600,000 rows and need to write it to MySQL. Instead of doing 600,000 separate queries to insert or update into the database, I would like to do just one.

I’m trying to also use the $wpdb->replace() method so it will update or insert if needed. I tried writing up the code, but I just can’t get it to work.

        $csvData = file_get_contents($upload_dir);
        $lines = explode(PHP_EOL, $csvData);
        $values = array();
        $array = array();
        $place_holders = array();
        $x=0;
        foreach ($lines as $line) {
            $array[$x] = str_getcsv($line);
            if($x > 0) {
                $email = $array[$x][2];
                $user_id = $array[$x][10];
                array_push($values, $user_id, $email, 0);
                $place_holders[] = "('%d', '%s', '%d')";
            }
            $x++;
        }

        //the below works, but only for an insert
        //$query = "INSERT INTO wp_users_test (ID, user_login) VALUES ";
        //$query .= implode(', ', $place_holders);
        //$wpdb->query( $wpdb->prepare("$query ", $values));

        //but this here doesn't work for the insert or update           
        $wpdb->replace("wp_users_test",$values);


Source: wpdb

Leave a Reply