Dragonrust » Blog Archive » Database Fetching with AJAX and PHP

Home
About

Database Fetching with AJAX and PHP

Posted by Zareef in JavaScript, PHP on 01 14th, 2012 | no responses

The main feature of a dynamic page is content fetching without refreshing the page. This is made easy by AJAX technology. An HTTP request is generated and sent to the server. After processing this request an answer is sent back to the browser. All of these happen without refreshing or leaving the page. Quite impressing, isn’t it?

Here you can find this tutorial easy to understand because it’s made for newbie’s. I think it will help you out through the end.

First we make a trigger event that sends the http request to the server. This can be set in a div content.

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<head>

<title>Database fetcher</title>

</head>

<body>

</body>

</html>

Here you can see that we have added a jQuery script in the head element. You can download the jQuery file and write the source or use any CDN. Here we used Google CDN. The button is named with and id “getdata” and also a name with same. This id is needed for working with jQuery. If you want to know more about jQuery visit jQuery documentation.

Now we would add the JavaScript with jQuery to handle the request.

$(‘#getdata’).click(function(){

$.ajax({

url: “getdata.php”,

type:’POST’,

dataType: ‘json’,

success: function(output_string){

$(“#result_table”).html(output_string);

} // End of success function of ajax form

}); // End of ajax call

});

</script>

AJAX Engineering:

This statement -

$(‘#getdata’).click()

Means we want to bind the ‘click’ event to this button. In here you can see a function which is called with the ‘click’ event. Here you see a variable ‘$.ajax’ which means AJAX functions are called. [AJAX = Asynchronous JavaScript and XML]

Ajax is a powerful tool that can be used to send and retrieve data from the server without having to refresh the page.

An event happens: Click a button.

Request is sent to your server: send data from an input or something (it’s optional).

Server picks up your request

Server process your request

Server sends back a result.

After processing the result it sends back result to the browser, usually an object, as an XML format. After receiving the data you can use/show it in the web page with JavaScript. This is why this technique is called AJAX.

In order to get through this process you have to identify some values such as URL, data types. After receiving the result we would place a function in the success definition. That means whatever we get from the server we have to manipulate those in our page.

In this case, we have:

$(“#result_table”).html(output_string);

this div element.

The PHP Script:

In the AJAX script we defined a PHP file named ‘getdata.php’. Now we will write these PHP codes in that file. Here we put fetched data in a variable $ my_site.

getdata.php

<?php

include(“connect.php”);

//Query of database

$my_site = mysql_query(“SELECT * FROM my_site “)

or die(mysql_error());

//Output results

if(!$my_site)

{

mysql_close();

echo json_encode(“There was an error running the query: ” . mysql_error());

}

elseif(!mysql_num_rows($my_site))

{

mysql_close();

echo json_encode(“No results returned”);

}

else

{

$header = false;

$output_string = “”;

$output_string .= “<table border=’1′>\n”;

while($row = mysql_fetch_assoc($facebook))

{

if(!$header)

{

$output_string .= “<tr>\n”;

foreach($row as $header => $value)

{

$output_string .= “<th>{$header}</th>\n”;

}

$output_string .= “</tr>\n”;

}

$output_string .= “<tr>\n”;

foreach($row as $value)

{

$output_string .= “<th>{$value}</th>\n”;

}

$output_string .= “</tr>\n”;

}

$output_string .= “</table>\n”;

}

mysql_close();

echo json_encode($output_string);

Here we included a PHP file named ‘connection.php’. This file is for connecting and processing database file to execute. If it is not prepared yet, let assume that it’s been included. The task for this file is to connect to the database, fetch data from tables, assign it to a variable and send that back to the browser within http protocol. In that PHP file you can see a string concatenation operator ‘ .= ’ which attach the table data with a string. After retrieving the data from database we need to end the session and send content as a JSON object back to the browser. Finally we have this echo.

echo json_encode($output_string);

This statement print out the variable containing requested data.

Now we have to define the HTML code in our page.

HTML:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<head>

<title>Show Content</title>

</head>

<body>

Get Data.

</button>

</div>

$(‘#getdata’).click(function(){

$.ajax({

url: “getdata.php”,

type:’POST’,

dataType: ‘json’,

success: function(output_string){

$(“#result_table”).append(output_string);

}

});

</script>

</body>

</html>

You can add some CSS properties if you need. But these are the basic snippet to go. To get data more dynamically use jQuery.

So this is it. I hope you can find this article useful and implement it in your real life project. This code is pre-crafted to implement directly into WebPages. Thanks for reading this article. Happy coding.