PHP MySQL Insert Query

The INSERT INTO statement is used to insert new records in a MySQL table.

Inserting Data Into a MySQL Database Table

Now that you've understood how to create database and tables in MySQL. In this tutorial you will learn how to execute SQL query to insert records in a table.

Let's make a SQL query using the INSERT INTO statement with appropriate values, after that we will execute this SQL query through passing it to the mysqli_query() function to insert data in table. Here's an example, which adds a record to the persons table by specifying values for the 'person_id', 'first_name', 'last_name' and 'email_address' fields:

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $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 (person_id, first_name, last_name, email_address) VALUES (1, 'Peter', 'Parker', 'peterparker@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);
  • ?>

If you remember, from the preceding section, the 'person_id' field was marked with the AUTO_INCREMENT flag. This modifier tells the MySQL to automatically assign a value to this field if it is left unspecified while inserting a new record to the persons table. To see this in action, try adding another record using the following statement:

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $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_address) VALUES ('John', 'Rambo', 'johnrambo@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);
  • ?>

Now go the phpMyAdmin (http://localhost/phpmyadmin/) and check out the persons table data inside the demo database, you will see the new 'person_id' is assigned automatically by incrementing the value of previous 'person_id' by 1.


Insert Data Into a Database From an HTML Form

Let's create an HTML form that can be used to insert new records to persons table.

Creating the HTML Form

Here's a simple HTML form that has three text <input> fileds and a submit button.

Example

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

Retrieving and Inserting the Form Data

When a user clicks the submit button of the add record HTML form, in the example above, the form data is sent to 'insert.php' file. The 'insert.php' file connects to the MySQL database server, retrieves forms fields using the PHP $_POST variables and finally execute the insert query to add the records. Here is the complete code of our 'insert.php' file:

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Escape user inputs for security
  • $first_name = mysqli_real_escape_string($link, $_POST['firstname']);
  • $last_name = mysqli_real_escape_string($link, $_POST['lastname']);
  • $email_address = mysqli_real_escape_string($link, $_POST['email']);
  •  
  • // attempt insert query execution
  • $sql = "INSERT INTO persons (first_name, last_name, email_address) VALUES ('$first_name', '$last_name', '$email_address')";
  • 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);
  • ?>
 

Note:The mysqli_real_escape_string() function escapes special characters in a string and create a legal SQL string to provide security against SQL injection.

This is very basic example of inserting the form data in a MySQL database table. You can extend this example and make it more interactive by adding validations to the user inputs before inserting it to the database tables. Please check out the tutorial on PHP form validation to learn more about sanitizing and validating user inputs using PHP.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.