Using AJAX with MySQL Database: Beginner Tutorial
Posted February 22, 2010 by Nick Vogt in Programming
See my updated beginner guide here.
AJAX (asynchronous JavaScript and XML) is a method for using JavaScript and server-side programming languages to access the server and update specific elements without having to reload the current page.
To learn AJAX we will use a very simple example that utilizes JavaScript, PHP, and MySQL to update a DIV element on a page with dynamic content from the MySQL database. We will use two PHP files for this example:
index.php - The main file that we navigate to.
data.php - The file that generates data for our JavaScript. Users never access this page directly.
Traditionally, we would only require one PHP file, which would contain the PHP script that contacts the database and then displays the content. However, this would also require the entire page to be reloaded for each change. With AJAX, the main JavaScript, which resides on the index.php file, contacts the data.php file behind the scenes. The data.php file contains the PHP script, accesses the database, and then sends information back to index.php for the JavaScript to insert into the page.
It may be easiest to look at the code and jump right in:
index.php
data.php
Keep in mind that our data.php file can contain much more than one simple database query and echo statement. It can access and output anything that you normally would in PHP. You could run several queries, including edit queries, or you could access text and XML files. You can also pass name/value pairs to data.php, so that the content generated can be more dyanmic. To do this, replace ajax.open('GET', 'data.php', true); with ajax.open('GET', 'data.php?name=value', true); (where name=value is your name/value pair).
If there is any confusion please don't hesitate to leave a comment. I will attempt to update this guide to make it as simple as possible and change any areas that may be worded poorly.
AJAX (asynchronous JavaScript and XML) is a method for using JavaScript and server-side programming languages to access the server and update specific elements without having to reload the current page.
To learn AJAX we will use a very simple example that utilizes JavaScript, PHP, and MySQL to update a DIV element on a page with dynamic content from the MySQL database. We will use two PHP files for this example:
index.php - The main file that we navigate to.
data.php - The file that generates data for our JavaScript. Users never access this page directly.
Traditionally, we would only require one PHP file, which would contain the PHP script that contacts the database and then displays the content. However, this would also require the entire page to be reloaded for each change. With AJAX, the main JavaScript, which resides on the index.php file, contacts the data.php file behind the scenes. The data.php file contains the PHP script, accesses the database, and then sends information back to index.php for the JavaScript to insert into the page.
It may be easiest to look at the code and jump right in:
index.php
<html>
<head>
<script type="text/javascript">
// Establish the XML HTTP Request
// This is what allows JavaScript to access our PHP file
if(window.XMLHttpRequest)
{
// For modern browsers
ajax = new XMLHttpRequest();
}
else
{
// For IE6 and IE5 browsers
ajax = new ActiveXObject('Microsoft.XMLHTTP');
}
// Sends request to PHP file
// You can pass query string name/value pairs here if you want
// Example: data.php?name1=value1&name2=value2, etc
ajax.open('GET', 'data.php', true);
ajax.send(null);
// Runs function when state of our request variable changes
ajax.onreadystatechange = function()
{
// 4 means done; the data has been retrieved from data.php
if(ajax.readyState == 4)
{
// Put that data into the DIV element
document.getElementById('div').innerHTML = ajax.responseText;
}
}
</script>
</head>
<body>
<div id="div"></div>
</body>
</html>
<head>
<script type="text/javascript">
// Establish the XML HTTP Request
// This is what allows JavaScript to access our PHP file
if(window.XMLHttpRequest)
{
// For modern browsers
ajax = new XMLHttpRequest();
}
else
{
// For IE6 and IE5 browsers
ajax = new ActiveXObject('Microsoft.XMLHTTP');
}
// Sends request to PHP file
// You can pass query string name/value pairs here if you want
// Example: data.php?name1=value1&name2=value2, etc
ajax.open('GET', 'data.php', true);
ajax.send(null);
// Runs function when state of our request variable changes
ajax.onreadystatechange = function()
{
// 4 means done; the data has been retrieved from data.php
if(ajax.readyState == 4)
{
// Put that data into the DIV element
document.getElementById('div').innerHTML = ajax.responseText;
}
}
</script>
</head>
<body>
<div id="div"></div>
</body>
</html>
data.php
<?php
mysql_connect('ACCOUNT', 'USER', 'PASS');
mysql_select_db('DATABASE');
$query = mysql_query('SELECT * FROM table');
$row = mysql_fetch_array($query);
// Any HTML written to data.php will show up in the DIV in index.php
echo $row[column];
?>
mysql_connect('ACCOUNT', 'USER', 'PASS');
mysql_select_db('DATABASE');
$query = mysql_query('SELECT * FROM table');
$row = mysql_fetch_array($query);
// Any HTML written to data.php will show up in the DIV in index.php
echo $row[column];
?>
Keep in mind that our data.php file can contain much more than one simple database query and echo statement. It can access and output anything that you normally would in PHP. You could run several queries, including edit queries, or you could access text and XML files. You can also pass name/value pairs to data.php, so that the content generated can be more dyanmic. To do this, replace ajax.open('GET', 'data.php', true); with ajax.open('GET', 'data.php?name=value', true); (where name=value is your name/value pair).
If there is any confusion please don't hesitate to leave a comment. I will attempt to update this guide to make it as simple as possible and change any areas that may be worded poorly.
| Tags | ajax mysql php |
|---|
Comment on this post
Legacy Comments (5)
Nick | August 28, 2010 | 12:31 PM PST
It sounds like your database is being updated, so the code is working, but there is a problem when updating your page's content using the responseText from ajax page. Either the responseText isn't being returned properly or you aren't updating your page's elements using Javascript properly.
Aziz | August 28, 2010 | 6:13 AM PST
Hi
I have tried the code. It is doing well but it is not updating values automatically. I have to refresh the page every time.
can you please insist me?
my email is: azizsagi@yahoo.clom
Thanks
www.8gstore.com
I have tried the code. It is doing well but it is not updating values automatically. I have to refresh the page every time.
can you please insist me?
my email is: azizsagi@yahoo.clom
Thanks
www.8gstore.com
soulemane | March 14, 2010 | 7:45 AM PST
thanks for your supports ...!
soulemane | March 10, 2010 | 9:52 AM PST
i have already bookmarked this site...
soulemane | March 10, 2010 | 9:51 AM PST
hi!
All my greetings and congratulations to your team ...
You have made my life easy ,thanks to your incredible , simple and brief tutorial i have found the ajax gateways ...
I preparing my final semester project on web devel ,and this bundle of oxygen is going to more than expected for me ..
Once more ,thanks and keep going ...
bye.
mdumie23@iut-dhaka.edu
All my greetings and congratulations to your team ...
You have made my life easy ,thanks to your incredible , simple and brief tutorial i have found the ajax gateways ...
I preparing my final semester project on web devel ,and this bundle of oxygen is going to more than expected for me ..
Once more ,thanks and keep going ...
bye.
mdumie23@iut-dhaka.edu