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

Created: 8 December 2010 : by Trystan Lea

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:

and thats it! a basic example for using jquery ajax and php for fetching data from a database.

Download source files: ajax01.tar.gz

If you don't see anything check that you have javascript enabled in your browser.

mlee3680's picture

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

Perfect post. Here’s a tool that lets your build your online database without programming. There is no need to hand code PHP. Cut your development time by 90% http://www.caspio.com/

Guest's picture

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

Guest's picture

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

Just to make it clear for the real beginners.
If you were wondering what DB.php was, it is the below code.

<?php

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

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

You should remove it from the api.php and put it in another file called DB.php

Leave <?php and ?> in the api.php file though.

AnotherGuest's picture

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

See? I was just wondering about that. Thank you for the heads up.

Guest's picture

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

I'm trying to use the script with wordpress, but it does not work, I think it's a problem with the url, you can give me some tips to solve the problem? tks

Benjamin's picture

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

This tutorial is indeed very helpful.
Has anybody tried to do it with ???
I have this idea but I don't know where to start.

Thank you so much..

Benjamin's picture

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

Sorry.. but that should have an option tag before the "???"
input type select
option. list1 .option
option. list2 .option
option. list3 .option
...

thanks

Matt's picture

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

HI,

This is really a nice script. How can I display multiple rows ? I need a while in my PHP, this is doable but what to do in Jquery ?

Guest's picture

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

This very Very Simple and easy to understand how to use jquery

TrystanLea's picture

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

Good to hear it! thanks