is there an alternate to using a for loop to insert multiple queries

Question

I want to insert data from an array. Below is an example situation.

I grab all my friends available in friends list (fb) and store them in an array.

Now I want to insert their data ( name, fbid, birthday ) into a table.

Currently I'm doing this using a for loop below is an example code.

<?php

  $friendsname = $_POST['name'];
  $friendsfbid = $_POST['fbid'];
  $friendsbday = $_POST['birthday'];

  for($i<0;count($friendsfbid);$i++){

     $sql_query  = "INSERT INTO table (fbid, name, birthday) VALUES ('$friendsfbid[$i]','$friendsname[$i]','$friendsbday[$i]') ON DUPLICATE KEY UPDATE fbid='$friendsfbid[$i]', name='$friendsname[$i]', birthday='$friendsbday[$i]'";    

  }

?>

Now if I have 300 friends this will loop 300 times.

The more number of friends the more time its going to take to process the data.

Is there a way to avoid this or to increase the performance of the code. ?

Using PHP with mySQL

You might like to read the answers given here: stackoverflow.com/questions/1176352/… - and consider using PDO. — Cups
– Cups, Commented Aug 27, 2013 at 8:44

Prasannjit · Accepted Answer · 2013-08-27 07:40:44Z

5

Please See this query hope this will be improve our code and speed.

Avoid doing SQL queries within a loop

A common mistake is placing a SQL query inside of a loop. This results in multiple round trips to the database, and significantly slower scripts. In the example below, you can change the loop to build a single SQL query and insert all of your users at once.

foreach ($userList as $user) {

  $query = 'INSERT INTO users (first_name,last_name) VALUES("' . $user['first_name'] . '", "' . $user['last_name'] . '")';

  mysql_query($query);

  }

Instead of using a loop, you can combine the data into a single database query.

$userData = array();

foreach ($userList as $user) {

    $userData[] = '("' . $user['first_name'] . '", "' . $user['last_name'] . '")';

}

$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $userData);

mysql_query($query);

answered Aug 27, 2013 at 7:40

Prasannjit

7954 silver badges11 bronze badges

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

5 Comments

Dalton Pereira Over a year ago

Hi Thanks for your reply. I would like to know how to handle duplicate keys in this case ?

Prasannjit Over a year ago

Duplicate key means ?

Dalton Pereira Over a year ago

What if the data is already present ? i want it to update in this case so how will the implode statement work. (ref: INSERT INTO users(field,field3) VALUES (val1,val2) ON DUPLICATE KEY UPDATE field=val1); i was thinking of creating another array to store the ON DUPLICATE KEY UPDATE clauses and implode them like you did in the current answer

Prasannjit Over a year ago

yes you are right, you can make another array for duplicate key.

jthinam Over a year ago

@Prasannjit, What if I have a subquery for checking if multiple columns exists or not, and only inserts if, say, firstname and lastname, does not exists in the table?

Gerben Jacobs · Accepted Answer · 2013-08-27 08:04:44Z

2

Insert multiple rows at a time. See this example:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

Loop through php to generate the multiple rows data and issue a single insert at last appending the multiple rows

edited Aug 27, 2013 at 8:04

Gerben Jacobs

4,6134 gold badges36 silver badges56 bronze badges

answered Aug 27, 2013 at 7:38

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

Comments

Dejv · Accepted Answer · 2013-08-27 07:52:21Z

1

$sql = '';
foreach($friendsfbid as $key => $value){
   $sql .= INSERT INTO table (fbid, name, birthday) VALUES ('$value[$key]','$friendsname[$key]','$friendsbday[$key]') ON DUPLICATE KEY UPDATE fbid='$value[$key]', name='$friendsname[$key]', birthday='$friendsbday[$key]'";
}
mysql_query($sql);

You can stack your sql INSERTs into string and then run them by calling query function only once. That should speed the process up.

edited Aug 27, 2013 at 7:52

answered Aug 27, 2013 at 7:47

Dejv

9442 gold badges14 silver badges31 bronze badges

Collectives™ on Stack Overflow

is there an alternate to using a for loop to insert multiple queries

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related