Insert query - looping through array of data?

Question

I have an array of names. My table structure is:

id | name

What would be the best way to insert each name from the array in to a separate row in the table?

id | name
 1 | John
 2 | James

I was thinking about looping through the array in PHP bu there must be a better way?

This would be the perfect place to use a prepared statement and bound parameters. — andrewsi
– andrewsi, Commented Jun 3, 2013 at 14:09
define "best". Do you experience any problem with your current approach at the moment? — Your Common Sense
– Your Common Sense, Commented Jun 3, 2013 at 14:09
loop the array to create one multivalued INSERT statetment, example, INSERT INTO tableName VALUES (1, 'John'), (2, 'James') — John Woo
– John Woo, Commented Jun 3, 2013 at 14:11

Daryl Gill · Accepted Answer · 2013-06-03 14:12:09Z

3

Using MySQli For Example:

$DB = new mysqli ("Server","username","password","database");

$Array = array("Daryl", "AnotherName");
foreach ($Array AS $Names){
  $Query = $DB->prepare("INSERT INTO Table (Name) VALUES (?)");
  $Query->bind_param('s',$Names);
  $Query->execute();
  $Query->close();
}

Best possible way would be to loop through the array using a foreach to get the individual values.. Then perform an insert on the current value before looping to the next.

answered Jun 3, 2013 at 14:12

Daryl Gill

5,52511 gold badges41 silver badges71 bronze badges

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

Comments

invisal · Accepted Answer · 2013-06-03 14:34:11Z

0

A small improvement to @Daryl Gill for prepare better practice

// List of names to insert
$names = array("Daryl", "AnotherName");

// Prepare once
$sh = $db->prepare("INSERT INTO tblname (Name) VALUES (?)");

foreach ($names AS $name){
  $sh->bind_param('s', $name);
  $sh->execute();
}

// Free resource when we're done
$sh->close();

answered Jun 3, 2013 at 14:34

invisal

11.2k4 gold badges36 silver badges54 bronze badges

1 Comment

invisal Over a year ago

Well, I am not fan of mysqli (most of the time I use PDO). The 's' means the first parameters is a string type. See: php.net/manual/en/mysqli-stmt.bind-param.php

Collectives™ on Stack Overflow

Insert query - looping through array of data?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related