PHP : MySQL INSERT Statement
The INSERT INTO statement adds new records (rows) to a table. In this chapter we add data to the users table that we created in the previous chapter.
Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
- Write the column names and the values in the same order.
- Text and date values are written inside quotes. Numbers do not need quotes.
- The
idcolumn is not needed, because it is AUTO_INCREMENT.
Insert one record
<?php
require_once('db-connection.php');
$sql = "INSERT INTO users (name, mobile, email)
VALUES ('Ram', '9898989898', 'ram@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record created. ID: " . mysqli_insert_id($conn);
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
If the users table was empty
New record created. ID: 1
mysqli_insert_id() returns the id that MySQL gave to the new row. It is useful when you need that id for the next query.
Insert many records in one query
You can add many rows in one INSERT statement. Separate every row with a comma. It is faster than running many queries.
<?php
require_once('db-connection.php');
$sql = "INSERT INTO users (name, mobile, email) VALUES
('Sanjay', '9898969696', 'sanjay@example.com'),
('Pankit', '9898979797', 'pankit@example.com')";
if (mysqli_query($conn, $sql)) {
echo "Rows inserted: " . mysqli_affected_rows($conn);
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Tutorials Class - Output Window
Rows inserted: 2
Note:
The email column is UNIQUE. If you run the same script twice, the second run fails with a “Duplicate entry” error. That is the expected behaviour. From PHP 8.1 the error is thrown as an exception.
Insert data from a form safely
When the values come from a form, never write them directly in the query, because of SQL injection. Use a prepared statement.
<?php
require_once('db-connection.php');
$name = $_POST["name"];
$mobile = $_POST["mobile"];
$email = $_POST["email"];
$stmt = $conn->prepare("INSERT INTO users (name, mobile, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $mobile, $email);
$stmt->execute();
echo "Record added.";
?>
Learn the full method in PHP MySQL Prepared Statements. Next chapter: PHP : MySQL Select Data.
Congratulations! Chapter Finished. Learn more about the similar topics:
| Exercises & Assignments |
|---|
| Display MySQL data in an HTML table using mysqli_fetch_array() |