How to insert data into a MySQL table in PHP

Super Basics of PHP & MySQL of how to create a form that will store the data into a MySQL Table.

Step 1 : Create a connection file.

<?php 
/* 1. Instance Database . */
$con= mysqli_connect("localhost","root","db_password","your_database");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

You can save this file by naming it connection.php. And Please don’t forget to replace “db_password” and “your_database” placeholders with your own username and password for your database.

Step 2 : Create a form.

You need to create a form on your page. The following is a basic form that would be part of your page. You can put your own heading and other sections and elements to surround this form. But for the sake of example I am only writing the bare necessary part required.

<form action="action_page.php" method="POST">
	<input type="text" name="name" placeholder="name>
	<input type="text" name="email_id" placeholder="email>
	<input type="submit">
</form>

Now the above code is really simple. It is a form which has to input text fields and on submit field.

Important things that we should look for are.

Form action :
In the above example it is “action_page.php”
Form action tells when the page is submitted where will the data be sent and the page be redirected.

Form method:
In our example we are using the “POST” method.

There are basically two methods for a form “POST” & “GET”.
Post can send more data, is more secure where as GET method put the data into the address of the page.

You can research more on these differences. But for this example we will stick with the POST method.

Input fields and their names
We have to give unique names to our input fields by using name attribute of the input tag. It is important because by the same name we will receive these value on the page where the action takes us.
In our form we are using two named inputs which are.

1. name
2. email_id

We will be sending these to the “action_page.php” as we have defined in our action attribute of our form tag.

To access these fields we will be using
$_POST[“name] and
$_POST[“email_id”].

Step 3 : Validating the data and inserting into the database.

Once the form is submitted all the data is sent to the action page. Which in our case is action_page.php. And all this data is accessible through $_POST array.

Because there are many character sets and special characters which the user might enter in the inputs. It is necessary for us to make sure these characters and special characters and handled properly. To this we escape these strings.

To escape the Special Characters before using them in a MySQL Statement. We will use mysqli_escape_string.

It escapes special characters in a string for use in an SQL statement, taking into account the current charset of the mysql Server

Special characters can cause errors while executing a SQL Command. So it is highly recommended to escape these before using them further.

in the below example we will escape the strings and save into a variable.

$name = mysqli_escape_string($con,$_POST["name"];

mysqli_escape_string function has two parameters.

Connection : This is important for the command to know the character set of the db so that it can escape accordingly.
String to escape : In our case it is the string that we got from our $_POST[“name”] that we expect to be passed to our action page from our form page.

After escaping both our values the code will look similar to.

$name = mysqli_escape_string($con,$_POST["name"];
$email = mysqli_escape_string($con,$_POST["email"];

Inserting into the database

MySQL server understands SQL Commands or statements. And we have to use the data to create a SQL command or statement that the server understands. If you do not know basic SQL it is highly recommended that you learn it. Here in this example we will be only using insert statement.

We need to tell the SQL server where to save the data and what data to save. So first we need to figure out the following.

a. Into which table we would be inserting the data.
b. What data we would be inserting into each field fo the selected table. 

In our example –

Table Name is : users
Field Names : full_name, email.

First we will write a SQL insert statement.
The Syntax of an insert statement is as follows.

Insert into <table_name>(field1,field2) values('value1','value2'); 

More about mysql insert syntax can be found on their Official Page.

So according to the syntax our statement would be .

insert into `users`(full_name,email) values('shishir','shishir.raven@gmail.com'); 

But hey! The data would be coming from the form and hence we need to replace it in our statement.

So we will create this string using PHP as shown below.

$sql = "insert into `users`(full_name,email) values('".$name."','" . $email."'); "

. (dot) is used to concatenate two strings in PHP.

So the final code should look like.

$name = mysqli_escape_string($con,$_POST["name"];
$email = mysqli_escape_string($con,$_POST["email"];
$sql = "insert into `users`(full_name,email) values('".$name."','" . $email."')";

Till now, we only have a sql statement or command in a variable.

But this command need to be sent to the SQL Server. We do this using mysqli_query() function in PHP

mysqli_query() function has two parameters.

1st Parameter is Connection.
2nd Parameters is SQL.

Find more about syntax here.

Using mysqli in our example would look like.

mysqli_query($con, $sql); 

Sometimes when we send a command to the mySql server errors can occur. In case of the errors we might want to know the error message and prevent further execution of the script.

To stop on error and display error messages we would use
or die() function and we will use mysqli_error() which shows the last error on mySQL server.

mysqli_query($con,$sql) or die(mysqli_error($con));

So now our final code should look like.

// Escaping Strings. 
$name = mysqli_escape_string($con,$_POST["name"];
$email = mysqli_escape_string($con,$_POST["email"];

// Creating a SQL Statement and storing into variable. 
$sql = "insert into `users`(full_name,email) values('".$name."','" . $email."')"; 

mysqli_query($con,$sql) or die(mysqli_error($con));

That’s it! ?
The values should get inserted into the database.

Data has been inserted into the database. Now the user might get a blank page since we have not written any code to show him any success message. You may want to redirect the user to a page where you have written a success message or whatever page you want him to land on.

To redirect we can use header function. In the following example if the following statement is executed the user will be redirected to a page called “success.php”

header("Location:success.php"); 

Please note:
If there are any output like by using echo or any spaces that are likely to be displayed on the page. header will show an error.

Final page should look like the following.

<?php 
include("connection.php");

// Escaping Strings. 
$name = mysqli_escape_string($con,$_POST["name"];
$email = mysqli_escape_string($con,$_POST["email"];

// Creating a SQL Statement and storing into variable. 
$sql = "insert into `users`(full_name,email) values('".$name."','" . $email."')"; 

mysqli_query($con,$sql) or die(mysqli_error($con));
header("Location:success.php"); 
?>

Don’t forget to create a success.php page.

Hope you will find this basic outline of how to insert data from a HTML form to mysql through PHP helpful.

Best of luck. ?
Keep experimenting.