Description
Insert is the process of inserting data into the table. INSERT statement adds new rows into already present table. The present chapter "PHP Insert Multiple Records Into MySQL" shows how to insert various records into the Database.
Multiple SQL statements uses
mysqli_multi_query() function to execute. Syntax
INSERT INTO table_name(column1,column2,...columnN) VALUES(value1,value2,...valueN);
If a string data types to be inserted, double or single quotes are to be used.
When adding a new row,the datatype of the value and the column should match.
Example
MySQLi (Object-oriented)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$HostName = "localhost";
$UserName = "root";
$Password = "password";
$dbname="accounts";
// Create connection
$connection = new mysqli($HostName, $UserName, $Password, $dbname);
// Check connection
if ($connection->connect_error)
{
die("Connection failed: " . $connection->connect_error);
}
//Inserting records into the database
$sql = "INSERT INTO users2 (Username, Password, First, Last)
VALUES ('Adam', 'password1', 'Adam', 'Gilchrist'),
('John', 'password2', 'John', 'Kennedy'),
('Michael', 'password3', 'Michael', 'Jackson')";
if ($connection->multi_query($sql) === TRUE)
{
echo "New records created successfully";
}
else
{
echo "Error: " . $sql . "<br>" . $connection->error;
}
$connection->close();
?>
</body>
</html>
[/php]
The output will look like below in phpmyadmin.
MySQLi Procedural
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "Student";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
//Inserting records into the database
$sql = "INSERT INTO users2 (Username, Password, First, Last)
VALUES ('Adam', 'password1', 'Adam', 'Gilchrist'),
('John', 'password2', 'John', 'Kennedy'),
('Michael', 'password3', 'Michael', 'Jackson')";
if (mysqli_multi_query($connection, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}
mysqli_close($connection);
?>
</body>
</html>
[/php]
Key Points
PHP Insert Multiple Records Into MySQL illustrates following important points.
- mysqli_multi_query() function inserts multiple records into the table.
- When adding a new row, the datatype of the value and the column should match.