The jQuery Ajax call will load the data from the database or from any file without refreshing the entire page. Using jQuery Ajax call the data loads asynchronously. This means the multiple functions can be executed at the same time not one by one. This is only possible by using Ajax. It optimizes the speed of the website and makes it fast than normal. Today, I will be showing you how to load data from the MySQL database into PHP using jQuery Ajax. If you don’t know how to submit form data in PHP MySQL without page refresh then please go through my previous post.
Ajax PHP Form Handling Using jQuery
jQuery Ajax Load
- The jQuery provides a
load()method. - This method is used to load the data from the server or the local system or from any file.
- It returns the data into the selected element which is defined for any specific area.
- jQuery load() method provides a very simple way to load the data asynchronously from the webserver.
- The basic syntax of this method is :
$(selector).load(URL, data, complete);The parameter inside the jQuery load() method has the following meaning:
- The first parameter (URL) specifies the source of the file that is to be load.
- The second parameter (data) which specifies the query string (i.e. key/value pairs) that is sent to the webserver along with the request.
- The last parameter (complete) is just a callback function that executes when the request completes. It is just like a response which returns after the execution completes.
Here is a simple example of load data using jquery ajax.
- Create a PHP file and paste the below code.
- Now, create a text file with some content. In my case, I have created a .txt file with name content.txt and written some content as-
This is programming fields.
<!DOCTYPE html>
<html>
<head>
<title>jQuery AJAX Load Method</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
$(document).ready(function() {
$("#loadBtn").click(function() {
setTimeout(function () {
$("#content").text("Please wait while loading...");
}, 2);
$("#content").load('content.txt');
});
});
</script>
</head>
<body>
<button type="button" id="loadBtn"> Load Data </button>
<div id="content"> </div>
</body>
</html>Save and execute the above code. You will see that the content of the .txt file will be loaded without refreshing the page.
Import CSV File Data into MySQL Database Using PHP
Retrieve MySQL Data in the HTML Table Using jQuery AJAX
In the next step, we are going to create a load more functionality using jQuery AJAX call in PHP. In this functionality, there will be a button. So, when you will click on the button, It will load 2 records from the database. On every click of the button, It will load the data from the database. Here, we will design a table layout along with a button. On the click of the button, the records will be loaded from the database without refreshing the page. So let’s create a database connection.
Dropdown Selection Filter in PHP Using jQuery and Ajax
Create a database connection in PHP
- First of all, create a new PHP file for creating the database connection. In my case it is db-confg.php.
<?php
$hostname = "localhost";
$username = "root";
$password = "root";
$dbname = "phpAjaxDemo";
$conn = mysqli_connect($hostname, $username, $password, $dbname)
or die("Database connection failed." .mysqli_connect_error());
?>- Now create a database if not created, or you can use the existing database.
- Create a new table with name registration.
- Here, is the database structure of the registration table.
CREATE TABLE `registration` (
`id` int(11) NOT NULL,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(20) NOT NULL,
`address` varchar(100) NOT NULL,
`city` varchar(20) NOT NULL,
`state` varchar(20) NOT NULL,
`zip_code` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Insert Records in the Table
- I have already inserted some records in the table.
Drag and Drop Multiple File Upload in PHP Using Dropzone js
Create a Table Using Bootstrap
- Now, we’ll be creating another PHP file named index.php. Here, we’ll be creating a table layout using bootstrap.
- So, include the bootstrap CDN or you can use the source file by downloading it.
- Here, is the source code of the index.php file.
<?php
include_once './db-config.php';
?>
<!doctype html>
<html lang="en">
<head>
<title>jQuery AJAX Load Data - Programming Fields</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="./load-data.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<!-- -------- Font awesome 5 kit -->
<script src="https://kit.fontawesome.com/44fb44d106.js"></script>
</head>
<div class="container pt-5">
<div class="row">
<div class="col-xl-12 col-lg-12 col-md-12 col-sm-12 col-12 m-auto d-block">
<table class="table table-striped" id="firstTable">
<thead class="bg-success text-white" id="firstThead">
<th> First Name </th>
<th> Last Name </th>
<th> Email </th>
<th> Address </th>
<th> City </th>
<th> State </th>
<th> Zipcode </th>
</thead>
<tbody>
<?php
$sql = "SELECT * FROM registration limit 2";
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0) {
$students = mysqli_fetch_all($result,MYSQLI_ASSOC);
foreach($students as $student) : ?>
<tr id="result">
<td><?php echo $student['first_name']; ?> </td>
<td><?php echo $student['last_name']; ?> </td>
<td><?php echo $student['email']; ?> </td>
<td><?php echo $student['address']; ?> </td>
<td><?php echo $student['city']; ?> </td>
<td><?php echo $student['state']; ?> </td>
<td><?php echo $student['zip_code']; ?> </td>
</tr>
<?php endforeach;
}
?>
</tbody>
</table>
</div>
</div>
<div class="row">
<div class="col-xl-6 col-md-6">
<button type="button" class="btn btn-info btn-sm" id="loadBtn"><i class="fa fa-refresh"></i> Load More.. </button>
</div>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>The above code is very simple to understand.
- At the top, I have included the db-config.php file in which, I have created the database connection.
- Then, created a table layout using the Bootstrap 4 classes.
- The bootstrap table contains the table body, in which I have created the PHP script for retrieving the records from the registration table which is shown above.
- In the table cell, I have printed the values.
- In the SQL query, I have used a limit of 2 so that the first time when you will run the script, It will print only 2 rows from the database table.
- Now, I’ll create a jQuery file for making AJAX request.
- Create a jQuery file with .js extension and paste the following code there. In my case the file name is load-data.js.
PHP File Upload Using jQuery and Ajax without Form Refresh
Create jQuery AJAX Function
$(document).ready(function() {
var recordCount = 2;
$("#loadBtn").click(function() {
recordCount = recordCount + 2;
$.ajax({
type: "GET",
url: "./load-data.php?count="+recordCount,
data: {},
contentType: "application/json; charset=utf-8",
dataType: "json",
cache: false,
success: function(response) {
var trHTML = '';
$.each(response, function (i, item) {
trHTML += '<tr><td>' + item.first_name + '</td><td>' + item.last_name +
'</td><td>' + item.email + '</td><td>' + item.address + '</td><td>' + item.city +
'</td><td>' + item.state + '</td><td>' + item.zip_code + '</td></tr>';
});
$('#firstTable').append(trHTML);
},
error: function (e) {
console.log(response);
}
});
});
});- In the above code, I have used the GET method to fetch the records from the database table using a PHP script. In the previous post, I had used the POST method. Actually, we’re inserting the records into the database table there.
- The data type is JSON (JavaScript Object Notation). It is a very lightweight data-interchange format.
- In the URL, I have passed a PHP file URL (load-data.php), in which I will create the PHP script for retrieving records from the database.
- After that, the response of success after the AJAX call, It will return the data from the PHP file.
- So, let’s create one more PHP file with named load-data.php because I have used the URL above. in the jQuery file.
<?php
include_once './db-config.php';
$recordNewCount = $_GET['count'];
$sql = "SELECT * FROM registration limit $recordNewCount";
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0) {
$data = mysqli_fetch_all($result,MYSQLI_ASSOC);
echo json_encode($data);
}
?>- In the above code, a SQL query has fired and It will return the data from the registration table and will convert it in the JSON format.
- Now, save and execute the index.php file. It will show the below result.
- Now, click on the Load more button, the 2 more records will be loaded in the same table without refreshing the page.
- On every click of the load more button, It will load 2 more records from the database table.
How to Use Yajra Datatables in Laravel 6
Conclusion
As you can see here, the records have been loaded after clicking the load more button. I hope guys the above example will help you to retrieve the data in the PHP using jQuery AJAX. The most important part of this jQuery AJAX call is to load the data asynchronously without refreshing the page.
Great sir, I was finding this type of tutorial on the web but didn’t find it in proper way. Really it is very helpful.
Hello sir, I am getting some error in the index.php file. Would you help me please?
Hey Mayank,
Please specify the error so that I can help you.
this is so helpful
thank you
It is helpful, Thanks for sharing.