PHP MySQL Insert Into Database Without Refreshing Page

Hello friends, in this PHP MySQL Insert into Database tutorial we will use jQuery AJAX method to insert values in our MySQL database without refreshing our web page. So lets begin.

What you need before starting with PHP MySQL Insert Tutorial?

WAMP / XAMPP server (I will be using wamp)
Notepad++ (notepad is also ok)
Little knowledge of PHP, SQL and JavaScript

Creating Database

First we will create a database
Go to phpmyadmin (localhost/phpmyadmin)
Create a database
Create a table inside the database named users

Creating Database

I have created a table name users. In users I have three columns (id, username and password) as you can see above. Id is primary key with an auto increment. So we will insert only username and password with our html form.

Creating HTML Form to Insert Values in Database

Now we will create our html form.
You can use the below html code to create your form.

<!DOCTYPE html>

<html>
<head>
	<title>PHP MySQL Insert Tutorial</title>
        <script src='https://code.jquery.com/jquery-2.1.3.min.js'></script>
</head>

<body>
	<form action='insert.php' method='post' id='myform' >
	<p>
	<input type='text' name='username' placeholder='user name' id='username' />
	</p>
<p>
	<input type='text' name='password' placeholder='password' id='password' />
	</p>
<button id='insert'>Insert</button>
	
	<p id='result'>
</p>
</form>
</body>
</html>

<!DOCTYPE html>

<html>

<head>

<title>PHP MySQL Insert Tutorial</title>

</head>

<body>

<p>

</p>

<p>

</p>

<button id='insert'>Insert</button>

</form>

</body>

</html>

As you can see we have one form with action = insert.php i.e. when we click on the button we will be redirected to insert.php.
The method is post and we have also given an id to our form which is myform.
The form has two inputs, one for username and other for password with name and id attribute.
We will use the id attribute to access the values from jQuery. So we have also added the jQuery file inside the head tag.
And at last we have a p element with an id result. This is to show the success message after successful insertion.
The above form will generate following output.

index.html

Creating PHP Script to Insert Values in Database

Create a file named insert.php
First we will connect to our database.
For connecting we will define some constants.

<?php
	define('HOST','localhost');
	define('USERNAME', 'root');
	define('PASSWORD','');
	define('DB','test');

<?php

define('HOST','localhost');

define('USERNAME', 'root');

define('PASSWORD','');

define('DB','test');

Now we will connect to our database using the constants we defined.

	$con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

1	$con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

Get the values which are coming from our form using post method.

	$username = $_POST['name'];
	$pass = $_POST['pass'];

1 2	$username = $_POST['name']; $pass = $_POST['pass'];

Finally we will insert these values into our database

	$sql = "insert into users (name, password) values ('$username','$pass')";
	
	if(mysqli_query($con, $sql)){
		echo 'success';
	}
?>

$sql = "insert into users (name, password) values ('$username','$pass')";

if(mysqli_query($con, $sql)){

echo 'success';

}

Your final php script for php mysql insert project is

<?php
	define('HOST','localhost');
	define('USERNAME', 'root');
	define('PASSWORD','');
	define('DB','mydatabase');
	
	$con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);
	
	$username = $_POST['name'];
	$pass = $_POST['pass'];
	
	$sql = "insert into users (username, password) values ('$username','$pass')";
	
	if(mysqli_query($con, $sql)){
		echo 'success';
	}
?>

<?php

define('HOST','localhost');

define('USERNAME', 'root');

define('PASSWORD','');

define('DB','mydatabase');

$con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

$username = $_POST['name'];

$pass = $_POST['pass'];

$sql = "insert into users (username, password) values ('$username','$pass')";

if(mysqli_query($con, $sql)){

echo 'success';

}

Now try running your project.
It is working fine but we are getting redirected to insert.php. Our task is to insert without refreshing our redirecting the page. For this we will use jQuery AJAX method.

Using jQuery AJAX Methods to insert values in database without refreshing the page

Create a new file name insert.js
Add your insert.js to your html file below the form

<script src='insert.js'></script>

1	<script src='insert.js'></script>

First we will stop our form from getting redirected to insert.php

$('#myform').submit(function(){
	return false;
});

$('#myform').submit(function(){

return false;

});

Try clicking insert button now, nothing will happen.
Now we will execute our php script in background using jQuery post method.

$.post(
	'script to execute',
	'values to send',
	function(result){
		//get the output of the script
	}
);

$.post(

'script to execute',

'values to send',

function(result){

//get the output of the script

}

);

We will use the above post method inside the click function of our button
So our final insert.js will be

$('#myform').submit(function(){
	return false;
});

$('#insert').click(function(){
	$.post(		
		$('#myform').attr('action'),
		$('#myform :input').serializeArray(),
		function(result){
			$('#result').html(result);
		}
	);
});

$('#myform').submit(function(){

return false;

});

$('#insert').click(function(){

$.post(

$('#myform').attr('action'),

$('#myform :input').serializeArray(),

function(result){

$('#result').html(result);

}

);

});

Try running your project now. It is working fine the values are getting inserted in database without refreshing our page.
You can also download the project source from the link given below. And if you are having any confusion or doubts following the steps feel free to comment. Thank You

PHP MySQL Insert into Database without Refreshing

Search This Blog

LearningSpot