Best practice inserting array to DB with PDO

Question

I'm trying to add array content to database. For it i'm using

$arr = ["10","11","12","13"];

foreach ($arr as $value) {
    $sql = "INSERT INTO `test` (user_id, view) VALUES (:user_id, :view)";
    $stmt = $this->conn->prepare($sql);
    $stmt->bindParam(':user_id', $value);
    $stmt->bindParam(':view', 'small');
    $stmt->execute();
}

is that good practice or bad? Is there any other good way?

Possible duplicate of [PDO Prepared Inserts multiple rows in single query](http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — hassan, Mar 01 '17 at 19:55

score 6 · Accepted Answer · answered Mar 01 '17 at 19:53

Proper example is something like this:

$arr = ["10","11","12","13"];

$sql = "INSERT INTO `test` (`user_id`, `view`) VALUES (:user_id, :view)";
$stmt = $this->conn->prepare($sql);

foreach ($arr as $value) {
     $stmt->execute(array('user_id' => $value, 'view' => 'small'));
}

score 3 · Answer 2 · edited Mar 01 '17 at 19:53

3

You can always execute() with an array:

$stmt->execute(array('user_id' => $value, 'view' => 'small'));

That usually minimizes your code, and it also allows you to accumulate data through if clauses as you build up a query without having to complicate your code.

Also as @u_mulder points out, you only need to prepare your query once as it doesn't change through each iteration of that loop.

edited Mar 01 '17 at 19:53

Alive to die - Anant

70,531
10
51
98

answered Mar 01 '17 at 19:51

tadman

208,517
23
234
262

Best practice inserting array to DB with PDO

2 Answers2