PHP MySQL Insert Data
Insert Data Into MySQL Using MySQLi and PDO
After creating a database and a table, we can begin adding information.
The following are some rules to follow in terms of syntax :
- In PHP the SQL query has to be quoted.
- You need to quotes string values inside the SQL query.
- Numerical values are not quoted.
- The name NULL cannot be quoted.
In the INSERT INTO declaration new records are added to a MySQL table :
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
We established an empty table called "Employee" with three columns : "id", "name", and "city" in the previous chapter.
Look at the table structure:
Column Name | Datatype | Extra |
---|---|---|
id | int | auto_increment |
name | text | |
city | text |
Let's start filling in the blanks in the table.
Note :- If there's AUTO INCREMENT column (like the column ID), the SQL query shouldn't have to specify it; MySQL adds the value automatically.
Example (MySQLi Object-oriented)
The examples below insert a new record into "Employee" table using Mysqli Object Oriented Style:
connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query
$sql = "INSERT INTO Employee (name, city) VALUES ('Suresh', 'Paris')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
// Close connection
$conn->close();
?>
After executing the above program the "Employee" table look like:
ID | Name | City |
---|---|---|
1 | Suresh | Paris |
Example (MySQLi Procedural)
The examples below add a new record into "Employee" table using Mysqli Procedural Style:
" . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>
Example (PDO)
The examples below insert a new row into "Employee" table using PDO Style:
exec($sql);
echo "New record created successfully";
} catch(PDOException $e) {
echo $sql . "
" . $e->getMessage();
}
$conn = null;
?>
Get ID of The Last Inserted Record
If the INSERT
or the UPDATE
of an AUTO INCREMENT table is carried out, the last inserted or modified record ID may be received instantly. This information is not available.
The "id" column of the table "Employee" is an AUTO INCREMENT field.
The example below is the same as on the above one but one single line of code has been added in order to get the ID of the last entry inserted. The latest inserted ID is also echoed :
Example (MySQLi Object-oriented)
The insert_id
property of mysqli
class is used to get the ID of last inserted records.
connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query
$sql = "INSERT INTO Employee (name, city) VALUES ('Babu', 'London')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully
";
$last_id = $conn->insert_id;
echo "Last inserted ID :- " . $last_id;
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
// Close connection
$conn->close();
?>
Output:-
Last inserted ID :- 2
After executing the above program the "Employee" table look like:
ID | Name | City |
---|---|---|
1 | Suresh | Paris |
2 | Babu | London |
Example (MySQLi Procedural)
The mysqli_insert_id()
method is used to get the ID of last inserted records.
$last_id = mysqli_insert_id($conn);
echo "Last inserted ID :- " . $last_id;
Example (PDO)
The lastInsertId()
function is used to get the ID of last inserted records.
$last_id = $conn->lastInsertId();
echo "Last inserted ID :- " . $last_id;
Related Links
Insert Multiple Records Into MySQL Using MySQLi
We can insert multiple statements at a time into Mysql using PHP Mysqli extension.
Note:- PDO will not support to execute multiple statement at once.
The examples below insert three new records at once into "Employee" table:
Example (MySQLi Object-oriented)
The multi_query()
method is used to insert multiple records at a time.
connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query
$sql = "INSERT INTO Employee (name, city) VALUES ('Kumar', 'New York');
INSERT INTO Employee (name, city) VALUES ('Siva', 'Paris');
INSERT INTO Employee (name, city) VALUES ('Rishi', 'London');";
if ($conn->multi_query($sql) === TRUE) {
echo "New record created successfully
";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
// Close connection
$conn->close();
?>
Note:- A semicolon must be used to divide each SQL statement.
After executing the above program the "Employee" table look like:
ID | Name | City |
---|---|---|
1 | Suresh | Paris |
2 | Babu | London |
3 | Kumar | New York |
4 | Siva | Paris |
5 | Rishi | London |
Example (MySQLi Procedural)
The mysqli_multi_query()
method is used to insert multiple records at a time.
if (mysqli_multi_query($conn, $sql)) {
echo "New record created successfully
";
For complete program, please refer procedural style program in the top of the page
Related Links