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, Jan 17 '15 at 15:38
I think this is already explained here: http://stackoverflow.com/questions/10054633/insert-array-into-mysql-database-with-php — TijmenBrx, Jan 17 '15 at 15:39
See color's mismatch in highlightning? You have syntax errors! — u_mulder, Jan 17 '15 at 15:40
@IndraKumarS - it's an alternative version of the INSERT syntax: http://dev.mysql.com/doc/refman/4.1/en/insert.html — andrewsi, Jan 17 '15 at 15:40
possible duplicate of [Inserting multiple rows in mysql](http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) — johnny, Jan 17 '15 at 15:43
How did you conclude this is inefficient?Premature optimization is the root of all evil.. — Mihai, Jan 17 '15 at 15:43

Arashium · Answer 1 · 2015-01-17T22:41:02.050

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();
}

How to insert multiple rows in to mySQL table

1 Answers1