Table of contents

PHP & MySQL AJAX example Using jQuery

Why do we need to use AJAX? AJAX can help us communicate to the server from the user interface without reloading our page and help to lessen the server bandwidth cost, and it improves user experience.

 

Ajax Example

 

Some big websites like Google, Youtube, Facebook, and many more using this technique, so if you are an aspirant web developer, you should learn AJAX.

 

In this tutorial, we will implement PHP & MySQL AJAX example using JQuery Library with simple Employee Saving & Getting records for us to test the POST & GET method AJAX.

 

So, we will start now with our code. Let's build our index.html below.

 

Index.html

<!doctype html>
<html lang="en">
<head>
  	<title>PHP & MySQL AJAX example Using jQuery</title>

  	<!-- Bootstrap CSS -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  	
  	<!-- Page CSS -->
  	<link rel="stylesheet" href="assets/css/styles.css">
</head>
  
<body>
   
	<div class="container">

		<br><br>

	    <h1>PHP & MySQL AJAX example Using jQuery</h1>

	    <br><br>
	    
	    <div class="row">
	    	<div class="col-md-4">
	    		<h3>Add New Employee</h3>

			    <form action="save.php" id="form">
			    	<div class="form-group">
					    <label for="email">Email</label>
					    <input class="form-control" type="text" name="email">
				  	</div>
				  	<div class="form-group">
					    <label for="first_name">First Name</label>
					    <input class="form-control" type="text" name="first_name">
				  	</div>
				  	<div class="form-group">
					    <label for="last_name">Last Name</label>
					    <input class="form-control" type="text" name="last_name">
				  	</div>
				  	<div class="form-group">
					    <label for="address">Address</label>
					    <textarea class="form-control" type="text" name="address" rows="3"></textarea>
				  	</div>
				  	<button type="button" class="btn btn-primary" id="btnSubmit">Submit</button>
				</form>
	    	</div>

	    	<div class="col-md-8">
	    		<h3>List of Employees</h3>
	    		<div id="employees-list"></div>
	    	</div>
	    </div>
	</div>

	<!-- Must put our javascript files here to fast the page loading -->
	
	<!-- jQuery library -->
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	<!-- Popper JS -->
	<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
	<!-- Bootstrap JS -->
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
	<!-- Page Script -->
	<script src="assets/js/scripts.js"></script>

</body>
  
</html>

 

Create Database Table

After the above is ready we must create our database table. We just skip creating a database just create your database or use our SQL dump on download files.

CREATE TABLE `employees` (
  `id` int(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `address` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employees`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

 

Save.php

After creating our table we will create a save.php file to process the save functionality using PHP & MySQL after we submit the data from the form via AJAX.

<?php
	$request = $_REQUEST; //a PHP Super Global variable which used to collect data after submitting it from the form
	$email = $request['email']; //get the date of birth from collected data above
	$first_name = $request['first_name']; //get the date of birth from collected data above
	$last_name = $request['last_name'];
	$address = $request['address'];

	$servername = "localhost"; //set the servername
	$username = "root"; //set the server username
	$password = ""; // set the server password (you must put password here if your using live server)
	$dbname = "demos"; // set the table name

	$mysqli = new mysqli($servername, $username, $password, $dbname);

	if ($mysqli->connect_errno) {
	  echo "Failed to connect to MySQL: " . $mysqli->connect_error;
	  exit();
	}

	// Set the INSERT SQL data
	$sql = "INSERT INTO employees (email, first_name, last_name, address)
	VALUES ('".$email."', '".$first_name."', '".$last_name."', '".$address."')";

	// Process the query so that we will save the date of birth
	if ($mysqli->query($sql)) {
	  echo "Employee has been created successfully.";
	} else {
	  return "Error: " . $sql . "<br>" . $mysqli->error;
	}

	// Close the connection after using it
	$mysqli->close();
?>

 

All.php

In this file, we create a function that will get all the employee's records which are executed via AJAX every to save the employee records and when reloading the page.

<?php
	$servername = "localhost"; //set the servername
	$username = "root"; //set the server username
	$password = ""; // set the server password (you must put password here if your using live server)
	$dbname = "demos"; // set the table name

	$mysqli = new mysqli($servername, $username, $password, $dbname);

	if ($mysqli->connect_errno) {
	  echo "Failed to connect to MySQL: " . $mysqli->connect_error;
	  exit();
	}

	// Set the INSERT SQL data
	$sql = "SELECT * FROM employees";

	// Process the query so that we will save the date of birth
	$results = $mysqli->query($sql);

	// Fetch Associative array
	$row = $results->fetch_all(MYSQLI_ASSOC);

	// Free result set
	$results->free_result();

	// Close the connection after using it
	$mysqli->close();

	echo json_encode($row);
?>

 

Scripts.js

Then our final code we put here our ajax functions for saving and getting employee's records. This code consists of these functions:

 

  • all() - which get all employees records via AJAX
  • submitForm() - use to store employee records via AJAX
  • resetForm() - use to reset the form after successfully created the employee

 

function all() 
{
	// Ajax config
	$.ajax({
        type: "GET", //we are using GET method to get all record from the server
        url: 'all.php', // get the route value
        success: function (response) {//once the request successfully process to the server side it will return result here
            
            // Parse the json result
        	response = JSON.parse(response);

            var html = "";
            // Check if there is available records
            if(response.length) {
            	html += '<div class="list-group">';
	            // Loop the parsed JSON
	            $.each(response, function(key,value) {
	            	// Our employee list template
					html += '<a href="#" class="list-group-item list-group-item-action">';
					html += "<p>" + value.first_name +' '+ value.last_name + " <span class='list-email'>(" + value.email + ")</span>" + "</p>";
					html += "<p class='list-address'>" + value.address + "</p>";
					html += '</a>';
	            });
	            html += '</div>';
            } else {
            	html += '<div class="alert alert-warning">';
				  html += 'No records found!';
				html += '</div>';
            }

            

            // Insert the HTML Template and display all employee records
			$("#employees-list").html(html);
        }
    });
}

function submitForm() 
{
	$("#btnSubmit").on("click", function() {
		var $this 		    = $("#btnSubmit"); //submit button selector using ID
        var $caption        = $this.html();// We store the html content of the submit button
        var form 			= "#form"; //defined the #form ID
        var formData        = $(form).serializeArray(); //serialize the form into array
        var route 			= $(form).attr('action'); //get the route using attribute action

        // Ajax config
    	$.ajax({
	        type: "POST", //we are using POST method to submit the data to the server side
	        url: route, // get the route value
	        data: formData, // our serialized array data for server side
	        beforeSend: function () {//We add this before send to disable the button once we submit it so that we prevent the multiple click
	            $this.attr('disabled', true).html("Processing...");
	        },
	        success: function (response) {//once the request successfully process to the server side it will return result here
	            $this.attr('disabled', false).html($caption);

	            // Reload lists of employees
	            all();

	            // We will display the result using alert
	            alert(response);

	            // Reset form
	            resetForm();
	        },
	        error: function (XMLHttpRequest, textStatus, errorThrown) {
	        	// You can put something here if there is an error from submitted request
	        }
	    });
	});
}

function resetForm() 
{
	$('#form')[0].reset();
}


$(document).ready(function() {

	// Get all employee records
	all();

	// Submit form using AJAX
	submitForm();
	 
});

 

I hope in this AJAX Example you will have enough foundation how to use the AJAX using jQUERY.

 

Recommendations:

  • Add form validations
  • Add cross-site request forgery (CSRF)
  • prevent from SQL Injection
  • Email validations if the correct format and the email is not yet existing in our database
  • User login so that the logged user can add employee

 

Download

 

Happy Coding :)

Read next