Creating A GuestBook

Part 3: Fetching and displaying the entries

Obviously this part is quite important. There's no point having a database full of guestbook posts if you don't know how to show them on your page, though it's also very simple.

Connecting

Exactly the same way we did when adding the message we need to connect to the database so that we can print out the messages, and we'll use much of the same code as before:


$db = mysql_connect('localhost','db_user','db_pass')
      or die(mysql_errno().' : '.mysql_error());

      mysql_select_db('db_name')
      or die(mysql_errno().' : '.mysql_error());

As before, change the values to connect to your own database. Then once we're connected, we can run a query to grab the information from the table.


$sql[getPosts] = mysql_query("SELECT name, email, message, date, FROM guestbook order by id DESC LIMIT 10");

Now this is slightly different from the query we used before, but it's quite easy to understand.

SELECT name, email, message, date FROM guestbook this part just says that we want to 'select' the name, email, message and date from our table named 'guestbook'.
order by id DESC LIMIT 10 This second part defines how we want to show the messages on our page order by id DESC if you remember when creating the table we added an "id" field that will give each entry a unique number, well this basically says we want to sort our messages in order according to their id number starting with the most recent first DESC = descending.
LIMIT 10 The final part, is exactly what it says, it will limit the output to 10 entries, so using this means only the last 10 messages will be displayed on our page, obviously you can change that however you like, if you don't specify a limit then all entries will be shown.

Display The Messages

Now that we've grabbed the information from the database we just need to print it onto the page. We need to create a loop, because we're grabbing a number of entries and not just one. You can use a while loop to make an array of the information we grabbed from the table.


while($data = mysql_fetch_array($sql[getPosts]))
{

Then print out each entry from the array. This is also the point where we'll convert the UNIX timestamp in the date field to a readable format using PHP's date() function.


    $post_date = date('D F jS Y @ g:ia', $data[date]);

    echo "<div>On $post_date <a href=\"mailto:$data[email]\">$data[user]</a> said:</div>\n";
    echo "<div>$data[message]</div>\n\n";
}

mysql_close($db);

After putting that together, you should have something like the following:


<?php

$db = mysql_connect('localhost','db_user','db_pass')
      or die(mysql_errno()'. : .'mysql_error());

      mysql_select_db('db_name')
      or die(mysql_errno()'. : .'mysql_error());

$sql[getPosts] = mysql_query("SELECT name, email, message, date, FROM guestbook order by id DESC LIMIT 10");

while($data = mysql_fetch_array($sql[getPosts]))
{
    $post_date = date('D F jS Y @ g:ia', $data[date]);

    echo "<div>On $post_date <a href=\"mailto:$data[email]\">$data[user]</a> said:</div>\n";
    echo "<div>$data[message]</div>\n\n";
}

mysql_close($db);

?>

And that's all you need, connect to the database, grab the entries from the table, put them into an array and use a loop to echo them one by one to the page.

Just like the error checking when submitting the form, there's many things you can ajust when displaying the output also, so just decide what you want to do and play around with it. Obviously you'll want to style the output to fit your pages.

That's as far as we go with this, not very advanced i know but hopefully a good starting point.

Since writing this tutorial i've added a few pages such as Error Checking In Forms and adding Clickable Smilies. Both of these could be used to help you develop the guestbook a little further.