CODEWITHSUNDEEP
phpmysql
puks1978
puks1978

Reputation: 3697

PHP bulk insert foreach

I have a curl script which reads the data from a remote source. Below is the current code:

function download_page($path){
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL,$path);
    curl_setopt($ch, CURLOPT_FAILONERROR,1);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION,1);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
    curl_setopt($ch, CURLOPT_TIMEOUT, 15);
    $retValue = curl_exec($ch);                      
    curl_close($ch);
    return $retValue;
}
$sXML = download_page('http://remotepage.php&function=getItems&count=100&page=1');
$oXML = new SimpleXMLElement($sXML);
foreach($oXML->results->item->item as $oEntry){
    $insert_query = mysql_query("INSERT INTO tbl_item (first_name, last_name, date_added) VALUES ('" . $oEntry->firstname . "', '" . $oEntry->lastname . "', '" . date("Y-m-d H:i:s") . "')");
}

The script works however is extremely slow to insert as I imagine its because its writing each individual record. The count variable is how many records returned for each page and the page variable is a simple page counter.

I am wondering if there is a way to do a bulk insert statement to insert all 100 records at once.

Thanks in advance.

Upvotes: 4

Views: 12839

Answers (4)

Repox
Repox

Reputation: 15475

Maybe something like this?

foreach($oXML->results->item->item as $oEntry){
  $inserts[] = "(". implode(', ', array ($oEntry->firstname, $oEntry->lastname, date("Y-m-d H:i:s") ) ) .")";
}
$insert_query = mysql_query("INSERT INTO tbl_item (first_name, last_name, date_added) VALUES ".implode(', ', $inserts));

Upvotes: 0

Rohit Choudhary
Rohit Choudhary

Reputation: 2259

to bulk insert operation you can append you script in a variable and you can run this as whole but for this you have to use mysqli_multi_query. Check this link http://www.php.net/manual/en/mysqli.multi-query.php

Upvotes: 0

Sanjeev Chauhan
Sanjeev Chauhan

Reputation: 4097

You can insert all records at once as below: Like when we export data from mysql table.

INSERT INTO tablename (id, field2, field3, field3) VALUES ('', 5454, '454', '545'), ('', 'erwe', 'rewrew', 'werew'), ('', 'ewrew', 'rwerwe', 'werwer'), ('', 'jkj', 'ere', 'uju') , ('', '343', '3434', 'dfdf');

Upvotes: 0

magnetik
magnetik

Reputation: 4431

You can do this within one statement by doing something like this :

$sXML = download_page('http://remotepage.php&function=getItems&count=100&page=1');
$oXML = new SimpleXMLElement($sXML);
$query = "INSERT INTO tbl_item (first_name, last_name, date_added) VALUES";
foreach($oXML->results->item->item as $oEntry){
    $query .=  "('" . $oEntry->firstname . "', '" . $oEntry->lastname . "', '" . date("Y-m-d H:i:s") . "'),";
}
mysql_query($query);

Upvotes: 5

Related Questions