PHP bulk insert foreach

Question

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.

Are you sure its the INSERT that is slow and not the cURL request? Try echoing some timing out to the page to make sure you know where its slowing down. — OdinX
– OdinX, Commented May 16, 2012 at 9:37
Please go through this php.net/manual/en/mysqli.multi-query.php — Rohit Choudhary
– Rohit Choudhary, Commented May 16, 2012 at 9:52
You can add multiple records in one insertion with the code that @magnetik, @chauhan or @Repox have provided. Or you can use prepared statements, that will help against SQL Injections (or use mysql_real_escape_string) and precompile the query so it will be faster if you execute multiple INSERT statements. — Carlos Campderrós
– Carlos Campderrós, Commented May 16, 2012 at 10:33

magnetik · Accepted Answer · 2012-05-16 09:36:07Z

5

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

answered May 16, 2012 at 9:36

magnetik

4,45643 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Campderrós Over a year ago

Remove the last comma from $query before inserting it to the database: $query = substr($query, 0, -1);

Sanjeev Chauhan · Accepted Answer · 2012-05-16 09:44:26Z

0

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

answered May 16, 2012 at 9:44

Sanjeev Chauhan

4,1273 gold badges26 silver badges31 bronze badges

Comments

Rohit Choudhary · Accepted Answer · 2012-05-16 09:49:35Z

0

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

answered May 16, 2012 at 9:49

Rohit Choudhary

2,2571 gold badge24 silver badges35 bronze badges

Comments

Repox · Accepted Answer · 2012-05-16 09:52:42Z

0

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

edited May 16, 2012 at 9:52

answered May 16, 2012 at 9:40

Repox

15.5k9 gold badges58 silver badges85 bronze badges

Collectives™ on Stack Overflow

PHP bulk insert foreach

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related