Rewriting code to use PDO and also improving efficiency for multiple inserts

Question

I have the following code, which I would like to be more efficient in performing inserts in to the db and also update the code to now use PDO:

foreach ($urls as $i => $url) {         


    //path to csv file
    $web = "http://path-to-file/".$url;

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_COOKIEJAR, "cookies.txt");
    curl_setopt($ch, CURLOPT_COOKIEFILE, "cookies.txt");
    curl_setopt ($ch, CURLOPT_URL, $web);
    curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt ($ch, CURLOPT_TIMEOUT, 30);
    $result = curl_exec($ch);

    //explode the csv file by new line
    $r = explode("\n", $result);

    $sql = "insert into product (`product_name`) values ";

    for ($i = 1; $i <= count($r); $i++){

        //explode each row by comma
        $n = explode(",", $r[$i]);                  
        $product_name = $n[0];


        $sql .= "('$product_name'),";


    }

    $sql = trim($sql, ",");

    if(count($n) != 0)
        mysql_query($sql);                  

}

Firstly, there is a loop which data from a remote file is retrieved and inserts all this data from the file into the database. At the moment the code inserts the data from one file and then moves on to the next file. Each file can have up to 2000 inserts, and usually done in batches of 15 (so rough total of records inserted is 30000). Would it be more efficient on the database to insert all 30000 records in one hit or, in batches? (bearing in mind there may be multiple users on the site also retrieving files and performing inserts).

Secondly I would like to modify the code above to use PDO.

Any help with this will be appreciated.

Thanks

PS. I do know how to create the PDO db connection.

Sorry, this is not the way to go for asking a question on Stackoverflow. Any reason you didn't try to read some online tutorial and to write the code yourself? — Your Common Sense, Mar 07 '13 at 05:10

score 1 · Accepted Answer · edited May 23 '17 at 11:43

To do it with the simplest code:

// Start PDO connection (DataBaseHandler)
$user = 'dbuser';
$password = 'dbpass';
$dbh = new PDO('mysql:host=localhost;dbname=database', $user, $password);

// Prepare a reusable insert statement and bind the variable
$insert = $dbh->prepare("INSERT INTO product (`product_name`) VALUES (:product_name)");
$insert->bindParam(':product_name',$product_name);

// Use foreach for arrays
foreach($r as $row){
    //explode each row by comma
    $n = explode(",", $row);                  
    $product_name = $n[0];

    // Execute the prepared INSERT statement referring to the current product name
    $insert->execute();
}

Not sure if doing ### INSERTS in one statement would be faster than doing ### separate INSERT statements, but this code is definitely much simpler and efficient thanks to PDO prepared statements: http://www.php.net/manual/en/pdo.prepare.php

EDIT:

According to this answer, it is much faster to do one big query. I hope I got you started with PDO and prepared statements, but you learn best by doing so I will leave it up to you to create the new PDO query. This might get you started: https://stackoverflow.com/a/6235710/703229

Rewriting code to use PDO and also improving efficiency for multiple inserts

1 Answers1