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.
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; ?>
This article contains demo and source codes. I hope this article helps you learning ajax, Feel free to give your comment below.
Tutorial Categories:
phpgang is a really good spot for learning web development. thanks for this useful tutorial Shahrukh ! ๐
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 :/
How to load specific data? i.e. name start with i or number