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

In this article, you will see how to fetch data from a mysql database using JQuery AJAX and php. JQuery AJAX allows us to update a page’s content without reloading the page.

1. Create Table

Create employee table and insert records.

create table employee(id int(10) not null primary key auto_increment, firstname varchar(20), lastname varchar(20));
insert into employee(firstname,lastname) values('Puneet','Aggarwal');
insert into employee(firstname,lastname) values('Sahil','Garg'); 

2. Create PHP

Now create a php file that connects to database and encode the result in JSON format.

Following is employee.php file

//connect to the mysql
$db = @mysql_connect('localhost', 'username', 'password') or die("Could not connect database");
@mysql_select_db('databasename', $db) or die("Could not select database");
//database query
$sql = @mysql_query("select firstname,lastname from employee");
$rows = array();
while($r = mysql_fetch_assoc($sql)) {
  $rows[] = $r;
//echo result as json
echo json_encode($rows);

3. Create Script

Now create a script file that calls employee.php using jquery ajax and then parse the response from employee.php

Following is ajax.js file

$(document).ready(function() {
    $("#ajaxButton").click(function() {
              type: "Post",
              url: "employee.php",
              success: function(data) {
                    var obj = $.parseJSON(data);      
                    var result = "<ul>"
                    $.each(obj, function() {
                        result = result + "<li>First Name : " + this['firstname'] + " , Last Name : " + this['lastname'] + "</li>";
                    result = result + "</ul>"

4. Create Front End

Now create a html file that displays the results parsed in ajax.js

Following is index.html file

<script language="javascript" type="text/javascript" src="jquery-1.6.2.js"></script>
<script language="javascript" type="text/javascript" src="ajax.js"></script>
<input type="button" value="Click Here" id="ajaxButton"/>
<div id="result"></div>

Following screen will be displayed when you click the button as shown in Figure 1.1

Figure 1.1 Figure 1.1

You can download the source code of this example here.


9 Responses to Using JQuery Ajax and php to fetch data from a mysql database