Insert Row intop MySQL Databse using PHP MySQL

A CRUD Application is a the one which a user can Insert, Update or Delete data from the database. In CRUD, C means Create, R means Read, U means Update and D means Delete. A CRUD application nothing but an HTML Form connected with a Table of any database. A user will enter the data in the form and then that data will get inserted into the table present in a database.

Look, till now, you have studied about performing operations on the database through MySQL queries and it is no different. The catch is that an average user won't know any SQl query, so it's your job to provide him a medium to enter data in the table by providing him a simple form for input. It is you, who is responsible to write a piece of code that will connect the form and the database together, so that the data entered by the user is properly inserted in the table and whenever needed the user should also be able to retrieve data from the tables.

For now, we will see how to insert data in the database taking inputs from a form.

Let's start, first thing you have to keep in mind is that you have to carefully create 3 different files. All 3 files will do different tasks. One file will take data from the user, the other one will establish a connection between the application and the database and the third one will insert the data into the database. Let's see an example:

Here we using 3 file for insert data in MySQL:

  • database.php : For establishing a database connection.
  • insert.php : It will have the HTML form to take inputs from user.
  • process.php : It will process the data and will send it to the database.

Let's see the step by step process to send data from a form to the database.

Step 1: Create a table

All the data from the HTML form will be stored to this table. You can see the SQL query to create a table named as 'employee' in the database. We are assuming that you already know, how to execute an SQL query, if not, then check out our previous tutorials.

Create table

CREATE TABLE `employee` ( `id` int(8) NOT NULL, `first_name` varchar(55) NOT NULL, `last_name` varchar(55) NOT NULL, `city_name` varchar(55) NOT NULL, `email` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2: Create database connection

We have used the XAMPP server. The 'MySQli' extension is used here to establish a connection. Here the database name is 'CRUD'.

File : database.php

<?php $servername='localhost'; $username='root'; $password=''; $dbname = "crud"; $conn=mysqli_connect($servername,$username,$password,$dbname); if(!$conn){ die('Could not Connect My Sql:' .mysql_error()); } ?>

Step 3: Create the HTML Form

Here's a simple HTML form which will take details from the employee. Let's see the highlights of the code:

  • action="process.php" : It specifies that all the form data will be submitted to 'process.php' file, where further processing will take place. The data is submitted after user clicks on the 'submit' button.
  • method="post" : It implies that the 'post' method of PHP will be used to send the data from this page to 'process.php'.
  • name="" : This attribute is used to give unique names to each input. These names are used to identify the data, until it is stored in the database. You will use these in 'process.php' file. Even the submit button needs a name.
File : insert.php

<!DOCTYPE html> <html> <body> <form method="post" action="process.php"> First name:<br> <input type="text" name="first_name"> <br> Last name:<br> <input type="text" name="last_name"> <br> City name:<br> <input type="text" name="city_name"> <br> Email Id:<br> <input type="email" name="email"> <br><br> <input type="submit" name="save" value="submit"> </form> </body> </html>

Step 3: Create Process File

The final and the most important page. All the data entered in the form is transferred to this page after the user submits the form. In this example we have used the 'post' function to transfer the data. At first the database file is included which is necessary to establish the connection. Then it is checked, whether the user has submitted the data or not by using the submit button's name.

  • $_POST[''] : It is used to collect values from HTML forms using method post. All the data, one by one, is transferred from $_POST variables to PHP variables.

At last the PHP variables containing the employee data are used to provide values in the insert query.

File : process.php

<?php include_once 'database.php'; if(isset($_POST['save'])) { $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; $city_name = $_POST['city_name']; $email = $_POST['email']; $sql = "INSERT INTO employee (first_name,last_name,city_name,email) VALUES ('$first_name','$last_name','$city_name','$email')"; if (mysqli_query($conn, $sql)) { echo "New record created successfully !"; } else { echo "Error: " . $sql . " " . mysqli_error($conn); } mysqli_close($conn); } ?>

This is very basic example of inserting the form data in a MySQL database table. You can make this example more attractive by applying css to the form. You can also add validations to user inputs. In the next tutorial we will go one step further, we will retrieve the data from the database and show it in the webpage.

Follow Us: