Tuesday, October 22, 2013

Using JQuery AJAX and php to fetch data from a mysql database

This is a brief example for fetching data from a mysql database using JQuery AJAX and php. JQuery AJAX allows us to update a page's content without reloading the page:
How it works
  1. First you access a content file on the server here I have called the file client.php.
  2. This file is downloaded to your computer and the html is displayed by your browser.
  3. Client.php contains javascript which is also downloaded to your computer.
  4. The jquery javascript then executes and creates its own request to a script on the server that has access to the mysql database. It does this using AJAX. This script in this example is php and is called api.php
  5. api.php executes and gets the data from the mysql database and returns it via a json string to the listening client.php script.
  6. The client.php script recieves this data and finally updates the html content on the page dynamically!
You will need
  • A LAMP (Linux-Apache-MYSQL-PHP) server or XAMP server to work with. There's loads of tutorials out there on installing LAMP on Ubuntu.
  • The jquery library inserted in the same directory as the scripts below
  • Download JQuery here: quick link : jquery-1.4.4.js

1) Create a database to store variable data

  1. Create a mysql database called ajax01
  2. Create a table called variables with 2 fields: id and name
  3. Insert an example variable:

2) Create a php script to receive http requests and fetch data from the database

  1. Create a php script called api.php on your server
  2. Copy and paste the example below and save it:
<?php 

  //--------------------------------------------------------------------------
  // Example php script for fetching data from mysql database
  //--------------------------------------------------------------------------
  $host = "localhost";
  $user = "root";
  $pass = "root";

  $databaseName = "ajax01";
  $tableName = "variables";

  //--------------------------------------------------------------------------
  // 1) Connect to mysql database
  //--------------------------------------------------------------------------
  include 'DB.php';
  $con = mysql_connect($host,$user,$pass);
  $dbs = mysql_select_db($databaseName, $con);

  //--------------------------------------------------------------------------
  // 2) Query database for data
  //--------------------------------------------------------------------------
  $result = mysql_query("SELECT * FROM $tableName");          //query
  $array = mysql_fetch_row($result);                          //fetch result    

  //--------------------------------------------------------------------------
  // 3) echo result as json 
  //--------------------------------------------------------------------------
  echo json_encode($array);

?>

3) Create a client script to fetch data from the API script using JQuery AJAX

  1. Create a html script called client.php in the same directory with the following content in it:
<!---------------------------------------------------------------------------
Example client script for JQUERY:AJAX -> PHP:MYSQL example
---------------------------------------------------------------------------->

<html>
  <head>
    <script language="javascript" type="text/javascript" src="jquery.js"></script>
  </head>
  <body>

  <!-------------------------------------------------------------------------
  1) Create some html content that can be accessed by jquery
  -------------------------------------------------------------------------->
  <h2> Client example </h2>
  <h3>Output: </h3>
  <div id="output">this element will be accessed by jquery and this text replaced</div>

  <script id="source" language="javascript" type="text/javascript">

  $(function () 
  {
    //-----------------------------------------------------------------------
    // 2) Send a http request with AJAX http://api.jquery.com/jQuery.ajax/
    //-----------------------------------------------------------------------
    $.ajax({                                      
      url: 'api.php',                  //the script to call to get data          
      data: "",                        //you can insert url argumnets here to pass to api.php
                                       //for example "id=5&parent=6"
      dataType: 'json',                //data format      
      success: function(data)          //on recieve of reply
      {
        var id = data[0];              //get id
        var vname = data[1];           //get name
        //--------------------------------------------------------------------
        // 3) Update html content
        //--------------------------------------------------------------------
        $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname); //Set output element html
        //recommend reading up on jquery selectors they are awesome 
        // http://api.jquery.com/category/selectors/
      } 
    });
  }); 

  </script>
  </body>
</html>

4) Test it!

  1. Goto client.php in your browser, if all is well you should see the following: