Insert multiple data with PHP

Question

I'm trying to insert multiple data in one table, however I got an error that says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO author (firstname, lastname) VALUES ('Rasmus', 'Lerdorf')INSERT INT' at line 1

Here's the method:

$sql = "INSERT INTO author (firstname, lastname)  VALUES ('Roal', 'Dahl')";
$sql .= "INSERT INTO author (firstname, lastname)  VALUES ('Rasmus', 'Lerdorf')";
$sql .= "INSERT INTO author (firstname, lastname)  VALUES ('Jane', 'Doe')";

Do I need to create a for loop for this? How could this work?

Unless you're using mysqli_multi_query or PDO::ATTR_EMULATE_PREPARES, you cannot use multiple inserts in one query. Separate them out. — aynber, Jan 30 '20 at 14:36

score 5 · Answer 1 · answered Jan 30 '20 at 14:37

5

You can do this :

  $sql = INSERT INTO author ( firstname, lastname) VALUES ('Roal', 'Dahl'), ('Rasmus', 'Lerdorf'), ('Jane', 'Doe');

answered Jan 30 '20 at 14:37

Tony S

491
6
26

score 3 · Answer 2 · answered Jan 30 '20 at 14:37

Generally, mysqli and PDO cannot execute multiple statements in a single query. There are alternatives like mysqli_multi_query, but that is not really what you need.

Your query can have multiple rows added by appending extra sections after VALUES like so:

$sql = "INSERT INTO author (firstname, lastname)  VALUES ";
$sql .= "('Roal', 'Dahl'), ('Rasmus', 'Lerdorf'), ('Jane', 'Doe')";

score -1 · Answer 3 · answered Jan 30 '20 at 14:50

You can use two options:

mysqli_multi_query

$sql = "INSERT INTO author (firstname, lastname)  VALUES ('Roal', 'Dahl');";
$sql .= "INSERT INTO author (firstname, lastname)  VALUES ('Rasmus', 'Lerdorf');";
$sql .= "INSERT INTO author (firstname, lastname)  VALUES ('Jane', 'Doe');";
if(!$mysqli->multi_query($sql)){
echo 'Error query:'. $mysqli->error . '.';
}else{
//do any operation
}

Or use one query like:

$sql = INSERT INTO author ( firstname, lastname) VALUES ('Roal', 'Dahl'), ('Rasmus', 'Lerdorf'), ('Jane', 'Doe');
if(!$mysqli->query($sql)){
echo 'Error query:'. $mysqli->error . '.';
}else{
//do any operation
}

Change $mysqli with your connection

Insert multiple data with PHP

3 Answers3