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:

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

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

<h2>Shoutbox</h2>
<form method="post" action="shout.php">
    Name: <input type="text" id="name" name="name" />
    Message: <input type="text" id="message" name="message" class="message" />
    <input type="submit" id="submit" value="Submit" />
</form>

<div id="shout"></div>
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:

 
$(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 +'&amp;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;
    });
});

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:

 
/*** 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 '<ul>';
    foreach ($dbh->query($sql) as $row) {
        echo '<li>';
        echo '<span class="date">'.date("d.m.Y H:i", strtotime($row['date_time'])).'</span>';
        echo '<span class="name">'.$row['name'].'</span>';
        echo '<span class="message">'.$row['message'].'</span>';
        echo '</li>';
    }
    echo '</ul>';
}

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.

 
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);

And that is what the lines:

if($_POST['refresh']) {
    populate_shoutbox();
}
 

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.

 Ref: http://www.codeforest.net/making-a-shoutbox-with-php-mysql-and-jquery