How can I insert multiple values using a loop?

Question

Here is my code:

$post_id = 10;
$tags_id = [23, 55, 155, 15];

$stmt = $dbh_conn->prepare("INSERT INTO post_tags  (post_id, tag_id) VALUES (?, ?)");
$stmt->execute([$post_id, $tags_id[0]]);
if ( !$stmt->rowCount() ){
    throwErrorMessage("Something went wrong while inserting tags");
}

As you can see, my code only inserts one row. I can count the number of count($tags_id) and copy-paste the whole code according to that number. But that approach doesn't seem good to me. Any idea what's the best approach to use an loop on that?

u_mulder · Accepted Answer · 2018-04-11 08:09:40Z

4

Prepare once and insert in a loop:

$stmt = $dbh_conn->prepare("INSERT INTO post_tags  (post_id, tag_id) VALUES (?, ?)");
foreach ($tags_id as $tag) {
    $stmt->execute([$post_id, $tag]);
    if ( !$stmt->rowCount() ){
        throwErrorMessage("Something went wrong while inserting tag " . $tag);
    }
}

edited Apr 11, 2018 at 8:09

answered Apr 11, 2018 at 8:06

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

2 Comments

u_mulder Over a year ago

And they are wrong. Prepared statements can be prepared once and used many times, that is the profit.

Kevin Over a year ago

@MartinAJ yes, that should be the way to go

feeela · Accepted Answer · 2018-04-11 08:41:30Z

2

You can either…

A) use a single statement and produce the VALUES (…) part in side a loop.

$values = [];
foreach ($tags_id as $tag) {
    $values[] = sprintf( '(%d, %s)', (int)$post_id, $dbh_conn->quote($tag) );
}

$stmt = $dbh_conn->prepare(
    sprintf(
        'INSERT INTO post_tags  (post_id, tag_id) VALUES %s;',
        implode(',', $values)
    ));

if(!$stmt->execute()) {
    // something went wrong
}

or

B) re-use the statement for one row per INSERT and call the execute inside a loop. (as suggested in the other answers)

edited Apr 11, 2018 at 8:41

answered Apr 11, 2018 at 8:07

feeela

30k7 gold badges61 silver badges73 bronze badges

2 Comments

Martin AJ Over a year ago

Thank you, how can I generate values() part dynamically?

feeela Over a year ago

@MartinAJ added quick example

Ranjit Shinde · Accepted Answer · 2018-04-11 08:22:30Z

0

Prepare the insert query and execute once. Try below code, it might be help

$post_id = 10;
$tags_id = [23, 55, 155, 15];
$lastElement = end($tags_id);
$insert = "INSERT INTO post_tags  (post_id, tag_id) VALUES";
foreach ($tags_id as $key => $value) 
{
    if ($value == $lastElement) {
        $insert .= "(".$post_id.", ".$value.")";
    } else {
        $insert .= "(".$post_id.", ".$value."),";
    }        
}
$stmt = $dbh_conn->prepare($insert);
$stmt->execute();
if ( !$stmt->rowCount() ){
    throwErrorMessage("Something went wrong while inserting tags");
}

edited Apr 11, 2018 at 8:22

answered Apr 11, 2018 at 8:10

Ranjit Shinde

1,1301 gold badge8 silver badges24 bronze badges

2 Comments

u_mulder Over a year ago

Such a mess of prepared statements and direct concatenation, please don't do that.

Martin AJ Over a year ago

Seems complex, but the logic is fine, thank you, upvote

Collectives™ on Stack Overflow

How can I insert multiple values using a loop?

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related