Making a shoutbox with PHP, MySQL and jQuery

Wikipedia says that : “A shoutbox, saybox, tagboard, or chatterbox is a chat-like feature of some websites that allows people to quickly leave messages on the website, generally without any form of user registration.”

I am going to show how you can easily build one of those, add some fancy awesomeness to it using jQuery AJAX. As the definition says, we need a simple form in which the user will enter his name and message. After the user submits the form, we will send the data through AJAX to our server side script, which will insert it into database and refresh the shoutbox. But, we have to refresh it for other users, too. I will show you how in the end of this tutorial. Check out the demo below and return to read the rest.

First, we will create a database table to hold our shoutbox data:

[code lang=”sql”]
CREATE TABLE IF NOT EXISTS `shoutbox` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` datetime NOT NULL,
`name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`message` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
[/code]

As I said, we need a simple form, so here is our markup:

[code lang=”html”]

Shoutbox

Name:
Message:

[/code]

Simple form and a div with id of shout, which will hold our shoutbox data. So, let me show you the jQuery code which will juice everything up and do the shouting magic:

[code lang=”javascript”]
$(function() {

$(“#submit”).click(function() {
// getting the values that user typed
var name = $(“#name”).val();
var message = $(“#message”).val();
// forming the queryString
var data = ‘name=’+ name +’&message=’+ message;

// ajax call
$.ajax({
type: “POST”,
url: “shout.php”,
data: data,
success: function(html){ // this happen after we get result
$(“#shout”).slideToggle(500, function(){
$(this).html(html).slideToggle(500);
$(“#message”).val(“”);
});
}
});
return false;
});
});
[/code]

I commented everything above, so go ahead and read it carefully. This code is collecting what user typed and sending it to server side script shout.php, empties the shout div and appending the new content. The return false is used so the form is not actually submitted.

On the server side I will show you how to use the PDO extension to securely issue the insert statement and some other magical things.

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.

So here is our shout.php file:

[code lang=”php”]
/*** mysql hostname ***/
$hostname = ‘localhost’;

/*** mysql username ***/
$username = ‘root’;

/*** mysql password ***/
$password = ”;

$dbname = ‘shout’;

try {
$dbh = new PDO(“mysql:host=$hostname;dbname=$dbname”, $username, $password);

if($_POST[‘name’]) {
$name = $_POST[‘name’];
$message = $_POST[‘message’];
/*** set all errors to exceptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = “INSERT INTO shoutbox (date_time, name, message)
VALUES (NOW(), :name, :message)”;
/*** prepare the statement ***/
$stmt = $dbh->prepare($sql);

/*** bind the params ***/
$stmt->bindParam(‘:name’, $name, PDO::PARAM_STR);
$stmt->bindParam(‘:message’, $message, PDO::PARAM_STR);

/*** run the sql statement ***/
if ($stmt->execute()) {
populate_shoutbox();
}
}
}
catch(PDOException $e) {
echo $e->getMessage();
}

/***** I WILL EXPLAIN THIS LATER *****/
if($_POST[‘refresh’]) {
populate_shoutbox();
}
/********************************/

function populate_shoutbox() {
// so we don’t have to connect again
global $dbh;
$sql = “select * from shoutbox order by date_time desc limit 10”;
echo ‘

‘;
foreach ($dbh->query($sql) as $row) {
echo ‘
• ‘;
  echo ‘‘.date(“d.m.Y H:i”, strtotime($row[‘date_time’])).’‘;
  echo ‘‘.$row[‘name’].’‘;
  echo ‘‘.$row[‘message’].’‘;
  echo ‘

‘;
}
echo ‘

‘;
}
[/code]

Let me explain a bit. First we are declaring some variables for db connection.

[code lang=”php”]
$dbh = new PDO(“mysql:host=$hostname;dbname=$dbname”, $username, $password);
[/code]

This is the actual connection to the database. It also selects the database (similar to mysql_selectdb).

Now that you’re connected via PDO, you must understand how PDO manages transactions before you start issuing queries. If you’ve never encountered transactions before, they offer 4 major features: Atomicity, Consistency, Isolation and Durability (ACID). In layman’s terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed. Transactional work can also be automatically undone at your request (provided you haven’t already committed it), which makes error handling in your scripts easier.

Transactions are typically implemented by “saving-up” your batch of changes to be applied all at once; this has the nice side effect of drastically improving the efficiency of those updates. In other words, transactions can make your scripts faster and potentially more robust (you still need to use them correctly to reap that benefit).

The interesting part is preparing statement for inserting into database. This is a higher level of security. The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur.

Other great advantage is that the query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle.

Our populate_shoutbox function is selecting the last 10 rows entered in the database and populate the Unordered list on the client side.

Now, you remember that I said that we will refresh the data for other users, so they can see what you actually typed in without the need to refresh the page.

On the client side I will add one function to the game. Its purpose is to populate the shoutbox the first time you arrive, and then it will fetch new data every 15 seconds. This is ok, as this is not chat.

[code lang=”javascript”]
function refresh_shoutbox() {
// we need some post data
var data = ‘refresh=1’;

$.ajax({
type: “POST”,
url: “shout.php”,
data: data,
success: function(html){ // this happen after we get result
$(“#shout”).html(html);
}
});
}

//populating shoutbox the first time
refresh_shoutbox();
// recurring refresh every 15 seconds
setInterval(“refresh_shoutbox()”, 15000);
[/code]

And that is what the lines:

[code lang=”php”]
if($_POST[‘refresh’]) {
populate_shoutbox();
}
[/code]

in shout.php are doing. If $_POST[‘refresh’] is received, it simply call the populate_shoutbox function.

This is it. I showed you how easy it is to implement the shoutbox functionality and why you should use the PDO extension to communicate with the database. In some tutorial to come, we will build a complete class for database operations using the PDO extension. So, stay tuned.