PHP MySQL Insert Data

In this tutorial, you will learn how to insert records into MySQL tables using PHP.

Insert data into MySQL database table

Now, you have learned how to create databases and tables in MySQL. In this tutorial, you will learn how to execute SQL queries to insert records into the table.

INSERT INTOused to insert a new row into the database table.

Let's use an INSERT INTO statement with appropriate values to execute an SQL query, and then we will execute the INSERT query by passing it to the PHP mysqli_query() function to insert data into the table. Here is an example that specifiesfirst_name,last_nameandemailThe value of the field is towardspersonsInsert a new row into the table.

<?php
$link = mysqli_connect("localhost", "root", "", "demo");
　
//　Check connection
if($link === false){
　　　　die("Error: Could not connect. " . mysqli_connect_error());
}
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', '[email protected]')";
if(mysqli_query($link, $sql)){
　　　　echo "Record insertion successful.";
}
　　　　echo "Error: Unable to execute $sql." . mysqli_error($link);
}
　
//Close connection
mysqli_close($link);
?>

<?php
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("ERROR: Could not connect. " . $mysqli->connect_error);
}
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', '[email protected]')";
if($mysqli-if(query($sql) === true){
　　　　echo "Record insertion successful.";
}
　　　　echo "Error: Unable to execute $sql." . $mysqli->error;
}
　
//Close connection
$mysqli->close();
?>

<?php
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
　　　　die("ERROR: Could not connect. " . $e->getMessage());
}
　
//Attempt to execute insertion query
try{
　　　　$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', '[email protected]')";　　　　
　　　　$pdo->exec($sql);
　　　　echo "Record insertion successful.";
}
　　　　die("Error: Unable to execute $_sql. $_e"->getMessage());
}
　
//Close connection
unset($pdo);
?>

If you rememberThe content of the previous chapter, thenidThe field is marked with the AUTO_INCREMENT flag. This modifier tells MySQL that if the value is not specified, it will increase the previous value by1to automatically assign a value to this field.

Insert multiple rows into the table

You can also insert multiple rows into the table in a single insert query. To do this, include multiple column value lists in the INSERT INTO statement, with each row's column values enclosed in parentheses and separated by commas.

Let's inpersonsInsert a few more rows into the table, as shown below:

<?php
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check connection
if($link === false){
　　　　die("Error: Could not connect. " . mysqli_connect_error());
}
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES"
　　　　　　　　　　　　('John', 'Rambo', '[email protected]'),
　　　　　　　　　　　　('Clark', 'Kent', '[email protected]'),
　　　　　　　　　　　　('John', 'Carter', '[email protected]'),
　　　　　　　　　　　　('Harry', 'Potter', '[email protected]')";
if(mysqli_query($link, $sql)){
　　　　echo "Record added successfully.";
}
　　　　echo "Error: Unable to execute $sql." . mysqli_error($link);
}
　
//Close connection
mysqli_close($link);
?>

<?php
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("ERROR: Could not connect. " . $mysqli->connect_error);
}
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES"
　　　　　　　　　　　　('John', 'Rambo', '[email protected]'),
　　　　　　　　　　　　('Clark', 'Kent', '[email protected]'),
　　　　　　　　　　　　('John', 'Carter', '[email protected]'),
　　　　　　　　　　　　('Harry', 'Potter', '[email protected]')";
if($mysqli-if(query($sql) === true){
　　　　echo "Record insertion successful.";
}
　　　　echo "Error: Unable to execute $sql." . $mysqli->error;
}
　
//Close connection
$mysqli->close();
?>

<?php
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
　　　　die("Error: Could not connect. " . $e->getMessage());
}
　
//Attempt to execute insertion query
try{
　　　　$sql = "INSERT INTO persons (first_name, last_name, email) VALUES"
　　　　　　　　　　　　('John', 'Rambo', '[email protected]'),
　　　　　　　　　　　　('Clark', 'Kent', '[email protected]'),
　　　　　　　　　　　　('John', 'Carter', '[email protected]'),
　　　　　　　　　　　　('Harry', 'Potter', '[email protected]')";　　　
　　　　$pdo->exec($sql);
　　　　echo "Record insertion successful.";
}
　　　　die("Error: Unable to execute $_sql. $_e"->getMessage());
}
　
//Close connection
unset($pdo);
?>

Now, go to phpMyAdmin (http://localhost/phpmyadmin/“)，and check”demonstrationin the databasePersontable data. You will findIDthe value of the column is obtained by adding the previousIDthe value of1to automatically increment.

Insert data from HTML form into database

In the previous section, we learned how to insert data into a database from a PHP script. Now, we will see how to insert data into a database obtained from an HTML form. Let's create an HTML form that can be used to insert new records intoPersontable.

Steps1Create an HTML form

This is a simple HTML form that contains three text<input>fields and a submit button.

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Add Record Form</title>
</head>
<form action="insert.php" method="post">
　　　　<p>
　　　　　　　　<label for="firstName">First Name:</label>/label>
　　　　　　　　<input type="text" name="first_name" id="firstName">
　　　　</p>
　　　　<p>
　　　　　　　　<label for="lastName">Last Name:</label>/label>
　　　　　　　　<input type="text" name="last_name" id="lastName">
　　　　</p>
　　　　<p>
　　　　　　　　<label for="emailAddress">Email Address:</label>/label>
　　　　　　　　<input type="text" name="email" id="emailAddress">
　　　　</p>
　　　　<input type="submit" value="Submit">
</form>
</html>

Steps2Retrieve and insert form data

In the above example, when the user clicks the submit button of the HTML form for adding records, the form data is sent to the "insert.php" file. The "insert.php" file connects to the MySQL database server, retrieves form fields using the PHP $_REQUEST variable, and finally executes an insert query to add the record. Here is the complete code for our "insert.php" file:

<?php
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check connection
if($link === false){
　　　　die("ERROR: Could not connect." . mysqli_connect_error());
}
　
//Escape user input for safety
$first_name = mysqli_real_escape_string($link, $_REQUEST['first_name']);
$last_name = mysqli_real_escape_string($link, $_REQUEST['last_name']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
if(mysqli_query($link, $sql)){
　　　　echo "Record added successfully.";
}
　　　　echo "Error: Unable to execute $sql." . mysqli_error($link);
}
　
//Close connection
mysqli_close($link);
?>

<?php
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("Error: Unable to connect." . $mysqli->connect_error);
}
　
//Escape user input for safety
$first_name = $mysqli->real_escape_string($_REQUEST['first_name']);
$last_name = $mysqli->real_escape_string($_REQUEST['last_name']);
$email = $mysqli->real_escape_string($_REQUEST['email']);
　
//Attempt to execute insertion query
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
if($mysqli-if(query($sql) === true){
　　　　echo "Record insertion successful.";
}
　　　　echo "Error: Unable to execute $sql." . $mysqli->error;
}
　
//Close connection
$mysqli->close();
?>

<?php
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
　　　　die("Error: Unable to connect. $_e"->getMessage());
}
　
//Attempt to execute insertion query
try{
　　　　//CREATE PREPARE statement
　　　　$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
　　　　$stmt = $pdo->prepare($sql);
　　　　
　　　　//Bind parameters to the statement
　　　　$stmt->bindParam(':first_name', $_REQUEST['first_name']);
　　　　$stmt->bindParam(':last_name', $_REQUEST['last_name']);
　　　　$stmt->bindParam(':email', $_REQUEST['email']);
　　　　
　　　　//Execute prepared statement
　　　　$stmt->execute();
　　　　echo "Record insertion successful.";
}
　　　　die("Error: Unable to execute $_sql. $_e"->getMessage());
}
　
//　Close connection
unset($pdo);
?>

In the next chapter, we will extend this insertion query example and implementPrepared Statementsto further improve security and performance, thereby further developing it.

This is a very basic example of inserting form data into a MySQL database table. You can extend this example to be more interactive by adding validation to user input before inserting it into the database table. Please seePHP Form Validationtutorial to learn more about using PHP to clean and validate user input.