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

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

First you access a content file on the server here I have called the file client.php.
This file is downloaded to your computer and the html is displayed by your browser.
Client.php contains javascript which is also downloaded to your computer.
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
api.php executes and gets the data from the mysql database and returns it via a json string to the listening client.php script.
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

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

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

Create a php script called api.php on your server
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

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!

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

Tuesday, October 22, 2013

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

1) Create a database to store variable data

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

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

4) Test it!