August 25, 2014 6:38 pm

Fetch data from XML, JSON & MySQLi using jQuery Ajax & PHP

Ajax (Asynchronous JavaScript and XML) is a technique to exchange the data with the server, updating part of a web page without reloading the page again. Using Ajax we can fetch data from various sources like XML, Json, Jsonp or MySQL Database. In this article you can learn how to implement ajax feature on your webpage and fetch data from various sources like XML, Json and Mysql database and display the data. I will be using jQuery ajax() function to do this task.

Fetch data from XML, JSON & MySQLi using jQuery Ajax & PHP

[wpdm_file id=111]DEMO

Setting up the HTML first.

The first thing you need to do is to make sure you have jquery library attached to the page.

<script src="jquery-1.9.0.min.js"></script>

The next part is to make three buttons and bind them with a function using onclick event.

<button type="button" onclick="fetchfromXMLfile();">Load Data from XML File</button>
<button type="button" onclick="fetchfromJsonfile();">Load Data from Json File</button>
<button type="button" onclick="fetchfromMysqlDatabase();">Load Data from Mysql Database</button>

Don’t worry about the functions; you will come to know about that soon. Now the final part itโ€™s to make three div containers with unique id to each. So each individual data will be placed on their specific container.

<div id="res1"></div>
<div id="res2"></div>
<div id="res3"></div>

Fetching Data from XML file using Ajax

To fetch a data from XML file make sure you have an XML file with data on the server. In our example we have a sample.xml file with some countries and their capital names.

<?xml version="1.0" encoding="utf-8"?>
<countries>
<country>
        <title>Afghanistan</title>
        <capital>Kabul</capital>
</country>
<country>
        <title>Australia</title>
        <capital>Canberra</capital>
    </country>
    <country>
        <title>Belgium</title>
        <capital>Brussels</capital>
    </country>
    <country>
        <title>Cuba</title>
        <capital>Havana</capital>
    </country>
</countries>

When you click on the button to fetch data from xml file the function fetchfromXMLfile(); will be fired and all the data will be fetch from that xml file. On successfully fetching the data, then you will have to display the data in a list format. Check the code below.

function fetchfromXMLfile() {
                  $.ajax({
                    type: "GET",
                    dataType: "xml",
                    url: "sample.xml",
                    cache: false,
                    beforeSend: function() {
                      $('#res1').html('loading please wait...');
                    },
                    success: function(xmldata) {
                      var str = '';
                      str += '<ul>';
                      $(xmldata).find('country').each(function() {
                        str += '<li>' + $(this).find('title').text() + ' - ' + $(this).find('capital').text() + '</li>';
                      });
                      str += '</ul>';
                      $('#res1').html(str);

                    }
                  });
                }

Fetching Data from Json file using Ajax

Json (JavaScript Object Notation) is a lightweight data-interchange format that is completely language independent also making it easy for humans to read and write. The technique to fetch data from Json is very similar to that of XML. Check a sample.json file below.

[{"title":"Afghanistan","capital":"Kabul"},{"title":"Australia","capital":"Canberra"},{"title":"Belgium","capital":"Brussels"},{"title":"Cuba","capital":"Havana"},{"title":"Egypt","capital":"Cairo"},{"title":"Fiji","capital":"Suva"},{"title":"India","capital":"New Delhi"},{"title":"Kenya","capital":"Nairobi"},{"title":"Nepal","capital":"Kathmandu"},{"title":"Pakistan","capital":"Islamabad"},{"title":"Singapore","capital":"Singapore City"},{"title":"Spain","capital":"Madrid"},{"title":"United Kingdom","capital":"London"},{"title":"Uzbekistan","capital":"Tashkent"}]

The Ajax code to fetch data from sample.json file and then iterate over the data and finally displaying it as a list.

function fetchfromJsonfile() {
                  $.ajax({
                    type: "GET",
                    dataType: "json",
                    url: "sample.json",
                    cache: false,
                    beforeSend: function() {
                      $('#res2').html('loading please wait...');
                    },
                    success: function(jsondata) {
                      
                      var str = '';
                      str += '<ul>';
                      $.each(jsondata, function(idx, obj) {
                        str += '<li>' + obj.title + ' - ' + obj.capital + '</li>';
                      });
                      str += '</ul>';
                      $('#res2').html(str);
                    }
                  });
              }

Fetching Data from MySQL Database using Ajax

Fetching data from mysql requires the usual approach. First you need to be connected with the database and then execute a query to the mysql database. Finally iterating over the data and displaying as list for the users. In our example when you click on the button to fetch data from mysql table, the function fetchfromMysqlDatabase() fires an ajax event which in turn calls a getrecords.php file. This file executes the query to the database and returns the data in html list format.

function fetchfromMysqlDatabase() {
                  $.ajax({
                    type: "GET",
                    dataType: "html",
                    url: "getrecords.php",
                    cache: false,
                    beforeSend: function() {
                       $('#res3').html('loading please wait...');
                    },
                    success: function(htmldata) {
                       $('#res3').html(htmldata);
                    }
                  });
                }

The code of the php file which executes the query to the database and return the data in a list format.

<?php
// This file makes a connection with mysql database. 
require_once("configure.php");

$sql = "SELECT title, capital FROM countries  WHERE 1 ORDER BY id asc";
$rs = mysqli_query($sql);
$str = '';

$str .= '<ul>';
while ($res = mysqli_fetch_array($rs)) {
  $str .= '<li>'.$res["title"].' - '. $res["capital"];
}
$str .= '</ul>';
echo $str;
?>
[wpdm_file id=111]DEMO

This article contains demo and source codes. I hope this article helps you learning ajax, Feel free to give your comment below.

Author Shahrukh Khan

Shahrukh Khan is a software engineer, blogger and a web lover. He loves to develop custom web solution and in between his free time blogs at thesoftwareguy.in. When he is not infront of the his computer (jenny) loves to hang around with friends. You can follow him on facebook, twitter or googleplus.


Tutorial Categories:

3 responses to “Fetch data from XML, JSON & MySQLi using jQuery Ajax & PHP”

  1. lightcode says:

    phpgang is a really good spot for learning web development. thanks for this useful tutorial Shahrukh ! ๐Ÿ™‚

  2. sai latha says:

    i haved an excel spreadsheet with xml extension … is it possible for me to upload my excel file in place of sample.xml to display my data…. ????

    actually i tried it but dint work out :/

  3. pjoshi235 says:

    How to load specific data? i.e. name start with i or number

Leave a Reply

Your email address will not be published. Required fields are marked *