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

<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

< ?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

< ?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

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

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

$(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

14 thoughts on “Fetch/Extract Data From Database Without Refreshing Webpage: jQuery”

rw1 says:
November 2, 2012 at 12:16 am
hi satish,
thank you very much for your very clear and well formatted tutorial.
i was wondering if you or anyone else could comment on the ‘security’ of this method from the ‘mysql injection’ angle. i have implemented one custom standalone php/mysql form in the past but it was username/password protected and someone looking at the code said that it wasn’t a secure method and vulnerable to mysql injections. so my question is, is the above method safe for a public facing web page?
thank you very much.
Reply
1. Satish says:
  November 2, 2012 at 5:51 pm
  @rw1, Since above tutorial is about fetching the data from database table, there is no question of SQL injection. When it comes to insertion of data we need to be careful. Using mysql_real_escape_string() PHP function around the user entered values(before processing it) would make the application more secure.
  Reply
2. delorme says:
  October 24, 2014 at 5:03 pm
  Where is this file jquery-1.8.2.min.js
  Reply
  1. Satish says:
    October 29, 2014 at 6:05 am
    @delorme, It’s jQuery library file and you’ll get it here.
    Reply

hello,

i have tried the above code and it is not outputting the database entries ie i get a blank page. firebug > console > errors shows ‘Firebug’s log limit has been reached. 486 entries not shown’. And the amount continues to increment. firebug > console > all shows incremental lines of: “GET: http :// path/to//fetch.php 200 OK 283ms” i don’t know if it makes a difference but the database i am trying to draw from has more than three fields, but i am only referencing three so as to follow this tutorial.

output.html:

< !DOCTYPE html>
<meta charset="UTF-8">
<html lang="en">
<head>
<title>Fetch/Extract Data From Database: jQuery + JSON + PHP+ AJAX</title>
</head>
<body>
<ul></ul>
<script type="text/javascript" src="http://path/to/jquery-1.8.2.min.js"></script>
<script type="text/javascript" src="http://path/to/my_script.js"></script>
</body>
</html>
</meta>

db.php

< ?php
      $conn = mysql_connect('localhost', 'user_name', 'password');
  $db   = mysql_select_db('user_dbname');
?>

fetch.php

< ?php
        include_once('http://path/to/db.php');
 
$sql = "SELECT * FROM dbtablename";
$res = mysql_query($sql);
$result = array();
 
while( $row = mysql_fetch_array($res) )
    array_push($result, array('field1' => $row[0],
                          'field2'  => $row[1],
  'another_field' => $row[2]));
 
echo json_encode(array("result" => $result));
?>

my_script.js

$(document).ready( function() {
 done();
});
 
function done() {
  setTimeout( function() { 
  updates(); 
  done();
  }, 200);
}
 
function updates() {
 $.getJSON("http://path/to/fetch.php", function(data) {
       $("ul").empty();
   $.each(data.result, function(){
    $("ul").append("<li>Name: "+this['field1']+"</li>
                            <li>Age: "+this['field2']+"</li>
                            <li>Company: "+this['another_field']+"</li>
                            <br />");
   });
 });
}

Satish says:
November 2, 2012 at 6:13 pm
@rw1, How many entries do you have in your table ?
If you have a lot of them.. try limiting the output: in fetch.php
$sql = “SELECT * FROM dbtablename LIMIT 1, 10”;
And one more thing is:
I guess, you’re getting confused with selecting the path of your php file.
The rule of thumb: you need to consider the index.html file(i.e., the file which to which the my_script.js file is linked). So give relative path and not absolute path to $.getJSON(“fetch.php”, );
Rest the scripts and the syntax looks totally fine.
Reply

rw1 says:
November 3, 2012 at 1:58 am
hello,
thank you for your reply.
i have changed the path names (one was wrong – moving from local file directory, ah!), but have also found this in firebug in my_script.js ‘SyntaxError: unterminated string literal’ on line 16 (using the code in the orignal post). i am trying to modify the syntax (something to do with single commas and double commas i think) but having no luck yet, running it through js lint also shows this message.
thank you.
Reply
1. Satish says:
  November 3, 2012 at 10:09 am
  @rw1, Write $(“ul”).append(); in one line and check the combination of double and single quotes..
  Reply
Live says:
January 18, 2013 at 12:56 pm
Thank you for your very simple tutorials and your time to teach them. You’ve helped a lot in my understanding of json.
However, I have been trying to make your code work, but it’s not working for hours, I’ve studied it.
A reader said he debugged using Firebug Console > Errors.
Indeed he’s correct. Please don’t use this syntax in your code, because it breaks, and it will only show a blank page:
function updates() {
$.getJSON(“fetch.php”, function(data) {
$(“ul”).empty();
$.each(data.result, function(){
$(“ul”).append(“Name: “+this[‘name’]+”
Age: “+this[‘age’]+”
Company: “+this[‘company’]+”
“);
});
});
}
///////////////////////////////////
INSTEAD use this
///////////////////////////////////
$(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(“Name: “+this[‘name’]+”Age: “+this[‘age’]+”Company: “+this[‘company’]+””);
});
});
}
THE DIFFERENCE, don’t break the .append(“……””);
All else it’s working fine! Thanks again
Reply
1. Satish says:
  January 18, 2013 at 1:20 pm
  @Live, Thanks a lot for your valuable input. I’m sure, it’ll help many.
  I use a editor that tackles this problem, maybe, so I was unaware of the problem.
  Thanks again, for taking time to write it.
  Reply
Atabick Cheema says:
November 30, 2014 at 9:01 am
Hi satish
i have try in every way but i got a blank page when i go to page via index.html.i also copy and paste your above code in all pages( fetch.php my_script.js..) but nothing ,please help me!
in fetch.php page showing normally index like {“result”:[{“name”:”atabick”,”age”:”35″,”company”:”masters”}]} but when i want to see in index.html page its show blank.
Reply
1. Satish says:
  November 30, 2014 at 9:24 am
  @Atabick Cheema, Make sure you’ve added the jQuery library before my_script.js
  Also check with the Chrome console window to see for any errors while you open index.html in the browser.
  Reply
Atabick Cheema says:
November 30, 2014 at 3:11 pm
Hi Satish!
Very thanks for your quick response.i have chcek also in chrome but their not showing any error please download my project from below link and tell me where is wrong,
http://www.mediafire.com/download/7gapvdkc1lzzjjo/fetch_data.zip
Reply
Atabick Cheema says:
November 30, 2014 at 3:33 pm
OMG
at least i think i have get solution it was in my_script.js …
$(“ul”).append(“Name: “+this[‘name’]+”Age: “+this[‘age’]+”Company: “+this[‘company’]+””);
above function/command must should be in one line.am i right?
Any way thanks a lot.
Reply

14 thoughts on “Fetch/Extract Data From Database Without Refreshing Webpage: jQuery”

Leave a Reply Cancel reply