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



Fetch/Extract Data From Database: jQuery + JSON + PHP+ AJAX

    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("
  • Name: "+this['name']+"
  • Age: "+this['age']+"
  • Company: "+this['company']+"

    • "); }); }); }

    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("
  • Name: "+this['name']+"
  • Age: "+this['age']+"
  • Company: "+this['company']+"

    • "); }); }); }

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


    Related posts:

    View Comments

    • 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.

      • @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.

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



Fetch/Extract Data From Database: jQuery + JSON + PHP+ AJAX

        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("
      • Name: "+this['field1']+"
      • Age: "+this['field2']+"
      • Company: "+this['another_field']+"

        • "); }); }); }

        • @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.

      • 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.

      • 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

        • @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.

      • 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.

        • @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.

      • 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.

      Leave a Comment

      Recent Posts

      • C

      C Program to Print Integer Numbers Till N

      • C

      Prime Numbers using Sieve of Eratosthenes: C Program

      • C

      Find Prime Numbers from 2 To N using Sieve of Eratosthenes: C Program

      • XRPL

      Verify The Transaction on Mainnet: XUMM SDK

      • XRPL

      Send Sign Request As Push Notification: XUMM SDK