Fetch Data from MYSQL using Jquery Ajax

This is really an interesting article where I will show you how to fetch data from mysql table using Jquery ajax. If you are familiar with PHP, then you may already know about how to fetch data using PHP. But in this tutorial I will show a completely different way of doing this. Ajax is a technology which extracts and displays the web content without loading the full page. I can update a single HTML <div> without loading the full web site. Now lets move to step by step procedures for pulling the out of mysql table and displaying in HTML table.

The first thing you need to do is create a simple MySQL database with some tables with some data. You can do this using simple SQL query but I have already a data of population of districts of Nepal in my database. I will use it in this tutorial. If you want to display different data then its ok just create a mysql table and insert some data in it.

Now write a simple PHP code that fetches the data from the table that you created. The PHP code connects to the database, selects the database, read the contents of the table and fetches the table rows. You can store each row in a different array with only those fields you need to display. Here is the code for this [lets say the file name as district.php]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

<?php  $conn = mysql_connect('localhost','root','');  if(!$conn){   die('Mysql connection error '.mysql_error());  }    $db = mysql_select_db('population',$conn);  if(!$db){   die('Database selection failed '.mysql_error());  }    $sql = 'SELECT *FROM pop_district';    $result = mysql_query($sql,$conn);      $data = array();  while($row = mysql_fetch_array($result)){   $row_data = array(    'name' => $row['name'],    'pop_male' => $row['pop_male'],     'pop_female' => $row['pop_female'],     'pop_total' => $row['pop_total']    );   array_push($data, $row_data);  }    echo json_encode($data); ?>

Here I made a simple array named $data. I push back each row to that array. And notice at the end of the code I have echoed the JSON encoded data. What this means? This is the most important step actually. I have converted the regular PHP array into JSON (JavaScript Object Notation) format. You can output in XML or JSON format. I like JSON because it is simple and easy for the beginner. The JSON is language independent. It is in name/value pair like Python dictionary or Java map. If I see the above file in browser having Jsonview extension. I will see the following output

It has exactly same attributes as I defined in php file. You can access those fields using simple . operator as you did in C structure or C++ Objects. For example to access the name I will write data.name, similarly for pop_male I will write data.pop_male.Now the next step is writing some Ajax code. I already said that I will use Jquery Ajax. Make a simple file and called it index.html. And write a simple ajax code that reads the above JSON formatted data and put it in the HTML table. The ajax code for this is

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<script type="text/javascript">   $(document).ready(function(){    var url = 'district.php';       $.getJSON(url, function(data) {           $.each(data, function(index, data) {            $('#tablebody').append('<tr>');        $('#tablebody').append('<td>'+data.name+'</td>');        $('#tablebody').append('<td>'+data.pop_male+'</td>');        $('#tablebody').append('<td>'+data.pop_female+'</td>');        $('#tablebody').append('<td>'+data.pop_total+'</td>');        $('#tablebody').append('</tr>');     });      });                     });                  });

\$.getJSON is a ajax way of getting JSON data from the url specified.
In our case the URL is ‘district.php’ because we have echoed the JSON formatted data there. Now \$.getJSON gets the whole record in the form of data. You can loop over each record using Jquery \$.each statement and display the each record using HTML table. A simple HTML table for this is

?

1 2 3 4 5 6 7 8 9 10 11 12 13

<table class="table table-striped">   <caption>Population of Districts of Nepal 2013</caption>   <thead>    <tr>     <th>District</th>     <th>Male Population</th>     <th>Female Population</th>     <th>Total Population</th>    </tr>   </thead>   <tbody id="tablebody">   </tbody>  </table>

If you give some attraction to the table using simple css and see the output in a browser. Your output will looks like