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?

Answer 1

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')";

Answer 2

You can do this :

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

Answer 3

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