PHP MySQL INSERT Query
Inserting Data into a MySQL Database Table
In the previous tutorial, we have studied the creation of tables in the database.
Now that you've understood how to create tables in MySQL we will go one
step ahead and will learn how to insert any data in the table with the help of SQL 'insert' query.
We form a query with the help of the "INSERT INTO" statement. This statement is used to start an 'Insert' query which inserts new records in the form of rows in a table. The "INSERT INTO" statement followed by the values of the columns in the table forms the 'Insert' query.
Let's see some syntax rules which one should follow to create an error-free query.
If these rules are violated then the query will not execute and no record will be inserted in the table:
Rules :
- All of the SQL queries must be written within quotes("").
- The word
NULL must not be quoted.
- The string values inside the SQL query should also be quoted.
- Never quote numeric values.
Now let's see the syntax of the whole query:
There are two types of syntax you can follow:
In the first form, the names of the columns are not specified and only the values are written. Have a look below -
INSERT INTO table_name
VALUES (value1, value2, value3,...)
In this type of syntax, you have to enter the values exactly in the sequence of the columns. This syntax inserts the values exactly like the sequence in which you insert it. For example, if the first column is ID, then whatever you will enter in place of value1 will be inserted into ID, value2 will be inserted into the 2nd column and so on. Be careful in this kind of approach because if the sequence is changed then you will end up having the wrong data in the columns or no data at all.
In the second form, you have to write both the column name and the values to be inserted. In this syntax, value1 will be inserted in column1, value2 in column2 and so on. Have a look below -
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Now we have to execute this query so we will use the mysqli_query() function.
Learn more about mysqli_query() function.
We will now see different examples of inserting data in the tables for different types of extension in MySQL.
Learn more about Different types of MySQL extensions.
Insert Record using MySQLi Procedural Method
In this example, the procedural method is followed. You can see that here we are inserting data into a table named persons, with columns 'ID', 'first_name', 'last_name', 'email' with their specified values. If the ID column is under AUTO_INCREMENT, then even if you won't give any value to it, it will take an incremented value from the previous value of the column.
Example
<?php
/* MySQL server connection. */
$link = mysqli_connect("localhost", "root", "", "demo");
/* Check connection */
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
/* Attempt insert query execution */
$sql = "INSERT INTO persons (id, first_name, last_name, email) VALUES (1, 'Peter', 'Parker', 'peterparker@mail.com')";
if(mysqli_query($link, $sql)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
/* Close connection */
mysqli_close($link);
?>
Output
Records inserted successfully.
Insert Record using MySQLi Object Oriented Method
This example focuses on executing the Insert Query using the Object-Oriented approach of the MySQLi extension.
Example
<?php
/* Attempt MySQL server connection. */
$mysqli = new mysqli("localhost", "root", "", "demo");
/* Check connection */
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
/* Attempt insert query execution */
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
if($mysqli->query($sql) === true){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
/* Close connection */
$mysqli->close();
?>
Output
Records inserted successfully.
PHP MySQLi Insert Record using PDO Method
The PDO method is described in the example below. To learn more about the PDO method you can go to MySQL Database,
where we have discussed the different extensions of MySQLi.
Example
<?php
/* Attempt MySQL server connection. */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
/* Set the PDO error mode to exception */
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
/* Attempt insert query execution */
try{
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
$pdo->exec($sql);
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
/* Close connection */
unset($pdo);
?>
Output
Records inserted successfully.
Inserting Multiple Rows into a Table
If you want to insert multiple queries, then you can easily add a new set of values in the query separated by a comma (,).
Look at the example below to see the syntax of inserting multiple values at once using different approaches.
Insert multiple records using Procedural Method
Example
<?php
/* Attempt MySQL server connection. */
$link = mysqli_connect("localhost", "root", "", "demo");
/* Check connection */
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
/* Attempt insert query execution */
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";
if(mysqli_query($link, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
/* Close connection */
mysqli_close($link);
?>
Output
Records inserted successfully.
Insert multiple record using Object Oriented Method
Example
<?php
/* Attempt MySQL server connection. */
$mysqli = new mysqli("localhost", "root", "", "demo");
/* Check connection */
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
/* Attempt insert query execution */
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";
if($mysqli->query($sql) === true){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
/* Close connection */
$mysqli->close();
?>
Output
Records inserted successfully.
Insert multiple records using PDO Method
Example
<?php
/* Attempt MySQL server connection. */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
/* Attempt insert query execution */
try{
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";
$pdo->exec($sql);
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
/* Close connection */
unset($pdo);
?>
Output
Records inserted successfully.