1

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?

Nikita Yunoshev
  • 399
  • 1
  • 4
  • 19
  • 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: https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql for the insert in mass format. – xQbert May 03 '18 at 12:43
  • check [this answer](https://stackoverflow.com/a/6889087/7393478) 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 May 03 '18 at 12:43
  • 1
    Possible duplicate of [Inserting multiple rows in mysql](https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) – Pankaj Makwana May 03 '18 at 12:47

3 Answers3

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.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • 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) – Kaddath May 03 '18 at 12:54
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)");
}
Ash-b
  • 705
  • 7
  • 11
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.

jspcal
  • 50,847
  • 7
  • 72
  • 76