Get data from database using AJAX, Javascript, PHP, MySQL

By the end of this tutorial, you will be able to get data from database using AJAX using simple Javascript (no jQuery). Benefits of using AJAX are:

1. Page loads faster as there will be no PHP script running during page load.
2. If you want to change data, you can do that easily without having to refresh the page.
3. You can show the same data on multiple pages, without having to rewrite the query. Just call the same javascript function.

Where AJAX is used ?

Google Earth

When you scroll in Google Earth, an AJAX request is sent to get the images of new location.

Facebook

When you scroll at the end of page in Facebook, an AJAX request is sent to get the older posts.

Charts & Graphs

In charts, when you change the date value, the charts has been updated to selected date via AJAX.

We are going to show records of employees from database table. We are using database named classicmodel, it will be included in the source files.

Create an HTML table

First we are going to create a file to show the data from database called index.php

Create a simple HTML table and give a unique ID to <tbody> tag

<table>
    <tr>
	<th>First name</th>
	<th>Last name</th>
	<th>Job title</th>
    </tr>

    <tbody id="data"></tbody>
</table>

Call AJAX to get data from database

To call an AJAX to get the data, first create an built-in XMLHttpRequest object and save in variable ajax.

<script>
    var ajax = new XMLHttpRequest();
</script>

Now call open(method, url, async) function from ajax object.

ajax.open("GET", "data.php", true);

• First parameter is the method of request GET or POST.
• Second is the name of file from where to get data. In this case data.php which will be created in next step.
• Third is a boolean, whether the request is asynchronous or not. true for asynchronous. Asynchronous requests does not hang up the browser. Syncronized requests may block the browser if the script at data.php is very time-taking.

You can also send headers with your request using the following methods:

ajax.setRequestHeader("Accept", "application/json")
ajax.setRequestHeader("Authorization", "Bearer {token}")

You can pass as many headers as you want by calling the method setRequestHeader multiple times.

Call the send() function from ajax object to actually call an AJAX request. It accepts no parameter.

ajax.send();

ajax object has multiple functions that are called once the status of request is changed. For example, when server receives the request, if server does not exists, if server failed to respond etc. All these callbacks are received in a function called onreadystatechange.

ajax.onreadystatechange = function() {
    //
};

Now we have to respond only if the request is successful. In this onreadystatechange function, we have 3 variables readyState, status and responseText.

• readyState will have value 4 when the request is done/completed.
• status will have value 200 when everything goes right.

ajax.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
    	console.log(this.responseText);
    }
};

Get data from MySQL database using PHP

Now create a new file named data.php and open it in code editor. First, you have to connect with database:

<?php
$conn = mysqli_connect("localhost", "root", "", "classicmodels");

• root is the name of database user.
• Third parameter is the password of database. For XAMPP/WAMP, it will be an empty string.
• classicmodels is the name of database.

Now, write your query to get the records from database. In my case, I am just getting all employees from database table named employees:

$result = mysqli_query($conn, "SELECT * FROM employees");

Create an array and loop through all database records returned from previous query, and save those records in that array:

$data = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($data, $row);
}

Now, whatever you echo will returned to the AJAX callback function in index.php file. In case of an array, you have to convert the array into JSON format and then echo it.

echo json_encode($data);
exit();

1. exit(); will prevent the code from further executing.
   
   At this point, if you run the index.php file, you will see similar to the following in your browser console:

Now, go back to index.php file and convert this JSON string back into an array:

ajax.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
    	console.log(this.responseText);
    	var data = JSON.parse(this.responseText);
    	console.log(data);
    }
};

JSON string should now be converted into an Javascript object named data.If you run index.php now and see the browser console, you will see something similar to the following:

Show data in HTML table

You got the data, now you just have to append it in table. Create an empty string variable named html:

var html = "";

Loop through all employees list:

for(var a = 0; a < data.length; a++) {
    //
}

Inside the loop, append the data in html variable:

for(var a = 0; a < data.length; a++) {
    var firstName = data[a].firstName;
    var lastName = data[a].lastName;
    var jobTitle = data[a].jobTitle;

    html += "<tr>";
        html += "<td>" + firstName + "</td>";
        html += "<td>" + lastName + "</td>";
        html += "<td>" + jobTitle + "</td>";
    html += "</tr>";
}

After the loop, append the html variable in <tbody> tag:

document.getElementById("data").innerHTML += html;

Now, your index.php file should look like this:

<table>
    <tr>
	<th>First name</th>
	<th>Last name</th>
	<th>Job title</th>
    </tr>

    <tbody id="data"></tbody>
</table>

<script>
    var ajax = new XMLHttpRequest();
    ajax.open("GET", "data.php", true);
    ajax.send();

    ajax.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            var data = JSON.parse(this.responseText);
            console.log(data);

            var html = "";
            for(var a = 0; a < data.length; a++) {
                var firstName = data[a].firstName;
                var lastName = data[a].lastName;
                var jobTitle = data[a].jobTitle;

                html += "<tr>";
                    html += "<td>" + firstName + "</td>";
                    html += "<td>" + lastName + "</td>";
                    html += "<td>" + jobTitle + "</td>";
                html += "</tr>";
            }
            document.getElementById("data").innerHTML += html;
        }
    };
</script>

And your data.php file should be like this:

<?php
$conn = mysqli_connect("localhost", "root", "", "classicmodels");
$result = mysqli_query($conn, "SELECT * FROM employees");

$data = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($data, $row);
}

echo json_encode($data);
exit();

Run the index.php file and you will see your data will be displayed via AJAX. So that’s how you can get data from the database using AJAX and display it in an HTML table.

There is more

This is just to get the data from the database. Follow this tutorial to learn complete CRUD (Create, Read, Update and Delete) operation in AJAX.

[wpdm_package id=’93’]