Automatically query MySQL and output results with AJAX

This is an extension of the post Refreshing DIV content with AJAX. I recommend you read it at first because you will understand better what the script below is about.

What this script does is the following: without having to press any refresh button, and not even reloading the whole page (but a DIV), it retrieves every X seconds the specified data from a MySQL database and displays in the DIV the refreshed contents of it. In other words, it queries the database regularly and updates the output.

In Refreshing DIV content with AJAX we had three files:

• ajax.js
• index.html
• boo.php

While the first and the second files remain untouched (so the code in them is exactly the one written in the aforementioned post), we make modifications to the third file, which is the one containing the code we want to periodically run. Instead of having the code obtaining the date and time, now we will make a MySQL query.

boo.php

Let’s suppose we have a table with three columns: name, gender and age; we want to get all the rows in the table and display them in the DIV. The code for boo.php would be:

<?php

// Configure connection settings

$db = 'dbname';
$db_admin = 'adminname';
$db_password = 'password';
$tablename = 'table1';

// Title

echo "Contents of the table:";

// Connect to DB

$sql = mysql_connect("localhost", $db_admin, $db_password)
or die(mysql_error());

mysql_select_db("$db", $sql);

// Fetch the data

$query = "SELECT * FROM $tablename";
$result = mysql_query($query);

// Return the results, loop through them and echo

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "<b>Column 1:</b> {$row['column1']}" .
"<b>Column 2:</b> {$row['column2']}" .
"<b>Column 3:</b> {$row['column3']}";
}

?>

(click to enlarge)

(For the script above to be refreshed automatically, remember you need the code of ajax.js and index.html found here).

An example of this script would return us something like the image to the right. Obviously as in any MySQL query the settings in the script can be configured in the desired way as well as the formating of the returned data. Remember the refresh rate is set in ajax.js.

As you can see there’s no secret here, it is the same code you would write for making a one-time MySQL query in a common PHP site.

What can the script be useful for? Possibly it has many uses but I can think of, for instance, showing a list with the users online and refreshing it every X minutes (supposing that their session has been stored in the database), or building a shoutbox or chat application which stores the entries in a MySQL database.

Final notes

I would not recommend setting the refresh rate in ajax.js to less than 5 seconds, in most of cases, even when it might seem less time is enough for executing the whole code. Depending on the extension of the code to be executed regularly it could lead to lag or malfunctioning. In any case it is good to try several rates and compare which performs best for your needs.

Related posts:

• OpenCart: display stock levels in storefront, as numbers or colour icons