Fetch/Extract Data From Database Without Refreshing Webpage: jQuery

Video tutorial illustrating, Fetching / Extraction of Data From Database Without Refreshing Webpage: jQuery + PHP + JSON + MySQL

You need not worry, even if you don’t know any one of these technology. Follow the tutorial, practice and understand, so that you could implement it for your applications.

Also read: Insert Data Into Database Without Refreshing Webpage.

In this tutorial we’ll be showing you, how to extract the data present in our MySQL database table without refreshing the webpage.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
<html>
<head>
<title>Fetch/Extract Data From Database: jQuery + JSON + PHP+ AJAX</title>
</head>
<body>
<ul></ul>
<script type="text/javascript" src="script/jquery-1.8.2.min.js"></script>
<script type="text/javascript" src="script/my_script.js"></script>
</body>
</html>

Here we have a unordered list. We’ll fill it’s list items by extracting the data from the database, using jQuery and PHP.

PHP File: Database Connection Code
db.php

1
2
3
4
< ?php
      $conn = mysql_connect('localhost', 'root', '');
  $db   = mysql_select_db('company');
?>

Here we’re connecting to the database called company.

Also Read: Connecting To MySQL server From PHP

PHP File: Extracting / Fetching Data From Database table and JSON Encoding
fetch.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
< ?php
        include_once('db.php');
 
$sql = "SELECT * FROM people";
$res = mysql_query($sql);
$result = array();
 
while( $row = mysql_fetch_array($res) )
    array_push($result, array('name' => $row[0],
                          'age'  => $row[1],
  'company' => $row[2]));
 
echo json_encode(array("result" => $result));
?>

Here we connect to the database by including db.php file.
Next, select all the fields from the people table.
Extract data one-by-one and store it inside the array variable $result, with key => value pair (associative array).
Finally, using PHP standard function json_encode(), we encode the fetched data.

jQuery File: Accessing The Data And Displaying
my_script.js

1
2
3
4
5
6
7
8
9
10
11
function updates() {
 $.getJSON("fetch.php", function(data) {
       $("ul").empty();
   $.each(data.result, function(){
    $("ul").append("<li>Name: "+this['name']+"</li>
                            <li>Age: "+this['age']+"</li>
                            <li>Company: "+this['company']+"</li>
                            <br />");
   });
 });
}

Using the jQuery AJAX method’s shortcut, $.getJSON() method, we parse fetch.php and process the data passed to the callback function.

We empty the unordered list to avoid duplicate entries later on.

Now loop through all the objects present in the jSON output of fetch.php
Using it’s key, retrieve it’s respective values and append it to the unordered list, as it’s list items.

jQuery File: AJAXing
my_script.js

1
2
3
4
5
6
function done() {
  setTimeout( function() { 
  updates(); 
  done();
  }, 200);
}

Call updates() function and itself[ done() ] inside setTimeout() function with a time gap of 200 milliseconds.

jQuery File: Full/Final Code
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$(document).ready( function() {
 done();
});
 
function done() {
  setTimeout( function() { 
  updates(); 
  done();
  }, 200);
}
 
function updates() {
 $.getJSON("fetch.php", function(data) {
       $("ul").empty();
   $.each(data.result, function(){
    $("ul").append("<li>Name: "+this['name']+"</li>
                            <li>Age: "+this['age']+"</li>
                            <li>Company: "+this['company']+"</li>
                            <br />");
   });
 });
}

Call done() once the document is loaded.
You could also call done() function once the window is focused or once some button is clicked or any other user events. This would reduce the database calls considerably.

Fetching / Extraction of Data From Database Without Refreshing Webpage: jQuery


[youtube https://www.youtube.com/watch?v=G_7oYeNU_3g]

YouTube Link: https://www.youtube.com/watch?v=G_7oYeNU_3g [Watch the Video In Full Screen.]



This would highly enhance the user experience and would help in building rich web applications which look more like desktop / standalone applications.

Please share this video with your friends and also LIKE it on social network sites and on YouTube. Thanks you <3

Related Read:
Insert Data Into Database Without Refreshing Webpage

Fetch JSON Array Elements Using jQuery AJAX Method: getJSON

Video tutorial illustrates the extraction of JSON Array elements using jQuery AJAX method: getJSON

Here we extract data(JSON ARRAY) from an external JSON file, which has a file extension .json

HTML code
index.html

1
2
3
4
5
6
7
8
<html>
<head><title>Fetch JSON array Data</title></head>
<body>
<ul></ul>
<script type="text/javascript" src="script/jquery-1.8.2.min.js"></script>
<script type="text/javascript" src="script/my_script.js"></script>
</body>
</html>

Here we have a unordered list. We’ll be filling it’s list items from the JSON file using jQuery.

JSON File
json_data.json

1
2
3
4
5
6
{
 "person": [  
     { "name": "Satish", "age": 25 },
     { "name": "Shwetha", "age": 24 }
 ]
}

Here we have a key called “person”, which is a JSON array.
“person” has two elements in it, which are objects.
Each object has two key => value pairs.
The key being, name and age.

jQuery Code
my_script.js

1
2
3
4
5
6
7
8
$(document).ready( function() {
 $.getJSON("json_data.json", function(data){
       $.each(data.person, function(){
         $("ul").append("<li>Name: "+this['name']+"</li>
                                <li>Age: "+this['age']+"</li>
                                <br />");
   });
 });

Once the document loads, we call an anonymous function. Inside that, we call the shortcut function of jQuery AJAX method i.e., $.getJSON method.
The first parameter is the file to be parsed, second parameter is the callback function.

Inside the callback function, we loop through the array elements and fetch the values using its key names, and finally append it to the unordered list of index.html

Fetch JSON Array Elements Using jQuery AJAX Method: getJSON


[youtube https://www.youtube.com/watch?v=VAMz-fcDNhs]

YouTube Link: https://www.youtube.com/watch?v=VAMz-fcDNhs [Watch the Video In Full Screen.]



To make the AJAX effect, you could write the jQuery coding inside a custom function and then call it for setTimeout() function etc:
setTimeout(customFunction, 4000);
setInterval(repeatFunctionCall, 4000);
slideDown().delay(4000).slideUp(); ( Chain method )

Fetch JSON Data Using jQuery AJAX Method: getJSON

Video tutorial illustrates fetching of JSON data using jQuery AJAX method, getJSON
Since its an AJAX method, the data is fetched without the need for refresh of the browser.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
11
12
<html>
<head><title>JSON jQuery AJAX</title></head>
<body>
 
<ul></ul>
 
<button>Users</button>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

Here we have a unordered list, without any list items in it. We’ll be fetching the list items from the JSON file using jQuery.
A button, clicking upon which we would fetch the Data inside jSON file.

JSON File
json_data.json

1
2
3
4
5
6
7
8
9
10
11
12
13
14
{
   "p1": { 
           "name": "Satish",
          "age":   25,
          "company": "Techntoip"
         },
 
 
   "p2": {
           "name": "Kiran",
          "age":   28,
          "company": "Oracle"   
         }   
}

Starts with a opening brace and ends with a opening brace.
In our example, we have taken two objects.
p1 and p2 are keys. It’s values are present inside opening and closing brace.
Each object has name, age and company info.

Also watch: Objects, Arrays: JSON Using jQuery.

jQuery Code
my_script.js

1
2
3
4
5
6
7
$("button").click( function() {
 $.getJSON( "json_data.json", function(obj) { 
  $.each(obj, function(key, value) { 
         $("ul").append("<li>"+value.name+"</li>");
  });
 });
});

Once the user clicks on the button, we invoke $.getJSON method.
First parameter we are passing is the URL of the json file. Next the call back function.

The call back function receives the jSON data which we have called as obj in our example.
Using $.each() method we iterate through all the objects present in our json file and then split them into key => value pair.

Now using the value we fetch the name present inside each objects value.

Video Tutorial: Fetch JSON Data Using jQuery AJAX Method: getJSON


[youtube https://www.youtube.com/watch?v=pe6keTE9LbE]

YouTube Link: https://www.youtube.com/watch?v=pe6keTE9LbE [Watch the Video In Full Screen.]




jSON & Database
We can generate jSON data out of a database and then fetch it using jQuery and display it on our web browser without the need for the modification of any code.
Whenever the json file or the database table data gets updated/modified, it instantly reflects on our web application.

Note:
You must open the file from a server. localhost works file too.
If you access the files directly from your computer folder, this application doesn’t work. So the files MUST be on a server.

Objects, Arrays: JSON Using jQuery

Video tutorial illustrates the basics of JSON: Create Object, Arrays, Access Elements, Modify/Update the value etc.

JSON full form: JavaScript Object Notation.

JSON is a lightweight data-interchange format.
It’s easy for humans to read and write.
It’s easy for machines to parse and generate.
So it’s more efficient at transferring data than XML.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
<html>
<head><title>JSON and jQuery!</title></head>
<body>
 
<ul></ul>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

We have a simple HTML structure, with a unorder list.
We’ll fill the list items from the JSON data using jQuery.

JSON and jQuery
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
$(document).ready( function() {
 
 var v1 = { "name": "Satish", "age": 25, "company": "Technotip IT Solutions" };
 var v2 = { "name": "Kiran", "age": 28, "company": "Oracle" };
 
 var obj = { "obj1": v1, "obj2": v2};
 
 var v3 = { "companies": [ "Microsoft", "Apple", "Google", "Oracle" ] };
 
  $("ul").append("<li>"+obj["obj1"]["name"]+"</li>");
  $("ul").append("<li>"+v3.companies[0]+"</li>");
});

Object
An object is a set of name/value pairs. An object begins with { and ends with }. Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

Array
An array is an ordered collection of values. An array begins with [ and ends with ]. Values are separated by , (comma).
Values in JSON can be string, number, array, objects, Boolean values or even null.

Fetch JSon Data From Object:
Object_name.Key;

Fetch JSon Data From Array:
Object_name.array_name[index_number];

Update JSON Data:

JSON and jQuery
my_script.js

1
2
3
4
5
 var v2 = { "name": "Kiran", "age": 28, "company": "Oracle" };
  $("ul").append("<li>"+v2.company+"</li>");
 
 v2.company = "Apple";
  $("ul").append("<li>"+v2.company+"</li>");

Would output
Oracle
Apple

Similarly, we could change the value of any key of an object or an array element.

Video Tutorial: Objects, Arrays: JSON Using jQuery


[youtube https://www.youtube.com/watch?v=MK39DQabaW8]

YouTube Link: https://www.youtube.com/watch?v=MK39DQabaW8 [Watch the Video In Full Screen.]



We could nest objects inside objects or arrays inside objects or objects inside an array etc, upto any level of complexities.
This is advantages for web applications like building menus with nested items, which are fetched from database.

Since, writing and fetching and parsing of JSON data is easy, most developers have stared using JSON over XML.