Insert an array to the database like a separate rows

Question

Sorry if this is a duplicate, but I can't reached the result using answers in similar questions.

Here is my situation: I have a table (users_temp) with 1 column - Users_id; And I have an array of values, for example - $usersIds = [1,2,3,4,5];

I want to Insert this array to my table and create 5 new rows. I was trying smth like that, but it doesn't work:

$newdata = "'" . implode("','", $usersIds) . "'";
db_query("INSERT INTO db.users_temp (user_id) VALUES ($newdata)");

Can you help me, please?

is user_ID string data or numeric? So your final query would be '1','2','3','4','5' I think instead of 's you want ()'s. See: stackoverflow.com/questions/6889065/… for the insert in mass format. — xQbert
– xQbert, Commented May 3, 2018 at 12:43
check this answer for a multiple insert in a single query. You can put the beginning of the query in a string, then loop on your array to add the values (value), with concatenation — Kaddath
– Kaddath, Commented May 3, 2018 at 12:43

Kickstart · Accepted Answer · 2018-05-03 12:49:01Z

2

Assuming that the $userIds field is safe (all INTs) then just amend your implode a touch.

$newdata = implode("),(", $usersIds);
db_query("INSERT INTO db.users_temp (user_id) VALUES ($newdata)");

You might want to add a check that the array has more than 0 elements.

answered May 3, 2018 at 12:49

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

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

1 Comment

Kaddath Over a year ago

elegant use of implode and special mention for the precision on the source data safety (it could be dangerous to concatenate strings that could contain unescaped characters or sql commands)

Ash-b · Accepted Answer · 2018-05-03 12:42:23Z

0

You can do this

$usersIds = [1,2,3,4,5];

foreach ($usersIds as $user_id) {
  db_query("INSERT INTO db.users_temp (user_id) VALUES ($user_id)");
}

answered May 3, 2018 at 12:42

Ash-b

7137 silver badges11 bronze badges

Comments

jspcal · Accepted Answer · 2018-05-03 12:52:42Z

0

To insert multiple rows in a single statement:

sql:

insert into users_temp (user_id)
values (?), (?), (?) [...]

php:

$userIds = array(1, 2, 3, 4, 5);
$placeholders = rtrim(str_repeat('(?),', count($userIds)), ',');
$sql = "insert into users_temp (user_id) values ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($userIds);

Your code can build up the placeholders string as above.

edited May 3, 2018 at 12:52

answered May 3, 2018 at 12:45

jspcal

52k7 gold badges76 silver badges79 bronze badges

Collectives™ on Stack Overflow

Insert an array to the database like a separate rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related