Retrieving database information with AJAX, PHP and MySQL

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:48 am - 1,757 views

(No Ratings Yet)

Loading ...

If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

With the recent popularization of AJAX (Asynchronus Javascript And XML), it is now unnessecary to reload a page with the same basic layout, just to get new content. You can use AJAX to retrieve just the content without reloading the page, then change the value of a content area.

First of all, to use AJAX you must understand how it works. AJAX is JavaScript that can run through the “AJAX Engine” and by doing so, retrieve data from a remote page through HTTP, without having a page reload. It is similar to PHP’s file_get_contents(), however it can be done through JavaScript as a client-side script.

The first thing we need, is a php file that we will call with AJAX. It doesn’t have to be PHP, but since it is the most popular server-side language, that is what we’ll use. If you have decent knowledge of another language you would like to use instead, I’m sure you could translate the code. In this file, we will use a GET variable to determine the MySQL query we need to perform. We are using GET because it is accessible by AJAX. The great thing about it is that the file you are calling can be incredibly simple, and just be straight textual information. Here is an example:

<?php

//This file is text.php

mysql_connect(“localhost”, “username”, “password”); //Connect to the mysql server with your host (most likely localhost), username, and password
mysql_select_db(“db_name”); //Select your database by name

$page = $_GET[“page”]; //This is the variable we retrieve through GET to know which row of content to retrieve

$sql = “SELECT * FROM pages WHERE page = ‘$page’”; //This is the text of the query. We will select the content field from the table ‘pages’ where the page field has the same value as the one we want to retrieve

$query = mysql_query($sql) or die(mysql_error()); //Make the actual query

if( mysql_num_rows($query) == 1 ) //Check to see if we found 1 row with that page name
{
$r=mysql_fetch_assoc($query); //Set a mysql fetching variable for the query
echo $r[“content”]; //Echo out the content of the page we want
}
else
{
echo “Sorry, that page was not found.”; //Otherwise, echo out an error message saying the page was not found
}

Now we have a file that will take in a page name through GET, query a row from our database with the same page name as that, then if that row exists, echo out the content of the page. We need a table in a database for this to work, however, so here is a very simple query to create a table with a field for page name and a field for content:

CREATE TABLE `pages` (
`page` VARCHAR( 255 ) NOT NULL ,
`content` TEXT NOT NULL
)

Next we need to create the AJAX function to call that file. I’ll start by posting the code then explain it:

You make have noticed that there are 4 lines of code that I placed asterisks by. I did that because I wanted to further explain them and tell you why they could be different for something else you might do with AJAX.

var file = ‘text.php?page=’; //This is the path to the file we just finished making *
The reason I starred this is becaused I didn’t have enough space to explain it in the comment. What you would have in this variable is your file (text.php in this instance) but with a blank form of your GET variable attached (page=) and of course you need the question mark (?) inbetween to separate the file extension from the GET variable. The reason we have a blank GET variable at the end is because in the next line we add the page title on to the end of it.

xmlhttp.open(‘GET’, file + page, true); //Open the file through GET, and add the page we want to retrieve as a GET variable **
I actually already explained why I starred this line. This first declares we’re retrieving data through GET, then we have our file variable (see *) followed by the page title. What this would look like if we weren’t using variables would be something like: text.php?page=home (pretending that home is the name of the page we want to retrieve).

***

var content = xmlhttp.responseText; //The content data which has been retrieved ***
I just starred this to make sure it was understood that we are merely putting the response text from the XMLHttpRequest into the variable content so it’s easier to work with. You don’t have to name it content, however if you change it make sure you change all of the other places where it is used also.

****

document.getElementById(‘content’).innerHTML = content; //Change the inner content of your div to the newly retrieved content ****
Currently what this line does is it changes the inner content of your div to the newly retrieved content. In this case we are using a div with the id ‘content’. If you have basic knowledge of JavaScript, you could do whatever you wanted with the content by now. For this tutorial I wanted to put the content into a div, but you could do with it what you like!

What I’m going to do now is put up an example html page (we’ll call that index.html) with the JavaScript AJAX functions embedded in it and two divs (one for content and another for a couple of example links).

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1″ />
<title>Our wonderful AJAX page!</title>
<script language=“javascript” type=“text/javascript”>
function getPage(page){
var xmlhttp=false; //Clear our fetching variable
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’); //Try the first kind of active x object…
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’); //Try the second kind of active x object
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=‘undefined’) {
xmlhttp = new XMLHttpRequest(); //If we were able to get a working active x object, start an XMLHttpRequest
}
var file = ‘text.php?page=’; //This is the path to the file we just finished making *
xmlhttp.open(‘GET’, file + page, true); //Open the file through GET, and add the page we want to retrieve as a GET variable **
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) { //Check if it is ready to recieve data
var content = xmlhttp.responseText; //The content data which has been retrieved ***
if( content ){ //Make sure there is something in the content variable
document.getElementById(‘content’).innerHTML = content; //Change the inner content of your div to the newly retrieved content ****
}
}
}
xmlhttp.send(null) //Nullify the XMLHttpRequest
return;
}
</script>
</head>

</div>
</body>
</html>

And there you have it, you’ve now got everything you need to make a simple page system using AJAX, PHP and MySQL. Just as a note, you’ll need to actually create rows in your table with page names home and page2 for this to work Those are just examples though so you can use them as a model for other links.