Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/php-mysql-ajax-example-using-jquery
Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!
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.
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 File
Here is the complete source code of our 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
Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!
I hope you enjoy this simple tutorial. Kindly visit here https://codeanddeploy.com/blog/php/php-mysql-ajax-example-using-jquery if you want to download this code.
Top comments (2)
And if a user presses enter while in the form it won't trigger, why not just do a $(document).on("submit","#form",function.... Instead of submit form. Same for reset. Using string concat for the listing is also meh. Overall seems like you rather new to this? If your project doesn't use jQuery for other things then you shouldn't add it just for this. (I'm a long time fan of jQuery but I can't with good concious recommend it any more with how good js has become) (that PHP is a SQL injection waiting to happen, use pdo and prepared statements rather, mysqli is on its way out)
Might also be slightly better to split the get records thing into 2, a render and a fetcher function. Then on initial page load pass the records to the render (json_encode) and in your php have an if header is Ajax then return Json encodeed data else return whole html thing. So you only load the html thing once but every Ajax request to the same page only returns the Json. (This stops a flicker of the page while it has to execute a 2nd request just to get the data in, to load a page)
Thank you for your suggestion appreciated.