How to insert multiple rows in to mySQL table

Question

I've got an array called $changedcompanies and I want to insert the contents into my mySQL table.

Currently I'm using a for loop in php which must be an inefficent way of going about it:

for ($x=0;$x<count($changedcompanies);$x++){
    try {
        $sql = "INSERT INTO ecampaign_historyamend SET historyid = '".$lastid."', 
        companyid = '".$changedcompanies[$x]['id']."', 
        newcontactid = '".$changedcompanies[$x]['contactid']."'";
        $s   = $pdo->prepare($sql);
        $s->execute();
    }
    catch (PDOException $e) {
        $error = 'Error inserting history amend: ' . $e->getMessage();
        showerror($error);
        exit();
    }
}

Is there a way to design a mySQL query that will insert the whole array in one go?

You're using prepared statements incorrectly. You need to use placeholders instead of adding the variables each time - prepare the statement outside the for loop, and just execute it inside the loop, passing the different variables each time. — andrewsi
– andrewsi, Commented Jan 17, 2015 at 15:38
I think this is already explained here: stackoverflow.com/questions/10054633/… — TijmenBrx
– TijmenBrx, Commented Jan 17, 2015 at 15:39
@IndraKumarS - it's an alternative version of the INSERT syntax: dev.mysql.com/doc/refman/4.1/en/insert.html — andrewsi
– andrewsi, Commented Jan 17, 2015 at 15:40

Arashium · Accepted Answer · 2015-01-17 22:41:02Z

1

Connect them all, then run them. Do not forget the semicolon between commands:

$sql="";
for ($x=0;$x<count($changedcompanies);$x++){
        $sql.= "INSERT INTO ecampaign_historyamend SET historyid = '".$lastid."', 
        companyid = '".$changedcompanies[$x]['id']."', 
        newcontactid = '".$changedcompanies[$x]['contactid']."';";
}

try {
$s = $pdo->prepare($sql);
$s->execute();
}
catch (PDOException $e) {
$error = 'Error inserting history amend: ' . $e->getMessage();
showerror($error);
exit();
}

edited Jan 17, 2015 at 22:41

answered Jan 17, 2015 at 17:58

Arashium

3252 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

How to insert multiple rows in to mySQL table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related