Creating A Guestbook Using PHP and MySQL ( Part 2 )

Shortcuts

Page Details

Published by:

admin
on 12-20-2008

3 people found this article useful.

Article

Comments (19)

100% of people found this useful
Creating A Guestbook Using PHP and MySQL ( Part 2 )

[Edit Tags]

Welcome to the second part of this guestbook tutorial. In case you haven't read the first section then go here to read it.

In this second part we'll add some code to our previous guestbook script which will allow us to view the entries. Without further ado let's start working on it.

Viewing the entries

<?php
include 'library/config.php';
include 'library/opendb.php';

// ... the code to save guestbook entries

}
?>
<html>
<head>
<title>Guestbook</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">

// ... the rest of javascript code goes here

</script>
</head>
<body>



<?php

// prepare the query string
$query = "SELECT id,
                 name,
                 email,
                 url,
                 message,
                 DATE_FORMAT(entry_date, '%d.%m.%Y') ".
          "FROM guestbook ".
          "ORDER BY id DESC ";

$result = mysql_query($query) or die('Error, query failed');

// if the guestbook is empty show a message
if(mysql_num_rows($result) == 0)
{
?>
<p><br><br>Guestbook is empty </p>
<?php
}
else
{
// get the entries
while($row = mysql_fetch_array($result))
{
// list() is a convenient way of assign a list of variables
// from an array values
list($id, $name, $email, $url, $message, $date) = $row;

// change all HTML special characters,
// to prevent some nasty code injection
$name = htmlspecialchars($name);
$message = htmlspecialchars($message);

// convert newline characters to HTML break tag ( <br> )
$message = nl2br($message);
?>
<table width="550" border="1" cellpadding="2" cellspacing="0">
<tr>
<td width="80" align="left">
<a href="mailto:<?=$email;?>"> <?=$name;?> </a> </td>
<td align="right"><small><?=$date;?></small></td>
</tr>
<tr>
<td colspan="2"> <?=$message;?>
<?php

if($url != '')
{
// make the url clickable by formatting it as HTML link
$url = "<a href='$url' target='_blank'>$url</a>";
?>
<br> <small>Homepage : <?=$url;?></small>
<?php
}
?>
</td>
</tr>
</table>
<br>
<?php
} // end while

When you just created the guestbook, there are no entry in guestbook table. We use mysql_num_rows()to check how many guestbook entries we have. If mysql_num_rows() returns 0 that means the table is empty and we can print a message saying that the guestbook is empty.

If there are already entries in the guestbook we then loop to get all the rows. I use list() to extract the values of a row into the variables $id, $name, $email, $url and $message.

An additional step is needed for the $name and $message. For these two we use htmlspecialchars() before printing their value. This function will convert all special characters to HTML entities.

As an example suppose I enter the string <b>I am a wizard</b> in the message textarea. After applying htmlspecialchars() it will be converted to <b>I am a wizard</b>

What's the point of using htmlspecialchars()?

Well, the answer is because some people may try to abuse your guestbook. Some will enter a simple HTML bold formatted message like the example above but some may even try to input a javascript code in the message. As an example I could enter a script like this :

If I don't use htmlspecialchars() and show it as is then when we view the guestbook entries this code will continously open a new window of www.google.com. Won't do any harm if you have a popup blocker ready. But for those unlucky people who haven't got it installed will have their desktop filled with new windows in no time. Very annoying indeed.

One more thing added for $message. We also use the function nl2br() to convert any newline characters ( that's \r OR \n OR both ) into HTML break tags ( <br> ). Because web browser "ignores'" newline characters, we need nl2br() to preserve the message formatting. This way if you explicitly enter a three line message it will also be shown as a three line message.

Ok, now that we have the values ready we just need to put them in the HTML table. In above example I use <?=$name;?> to print the value of $name. I can also use <?php echo $name; ?>, but it's easier to use the first form.

Now we're one step closer to finishing the guestbook. We just need to add a little more code for paging. Surely you don't want to show all the entries in one page. If you have a hundred entries the page will take forever to load. So let's add that little code to split the result into multiple pages.

Showing the entries in multiple pages

<?php

// how many guestbook entries to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

// prepare the query string
$query = "SELECT id,
                 name,
                 email,
                 url,
                 message,
                 DATE_FORMAT(entry_date, '%d.%m.%Y') ".
          "FROM guestbook ".
          "ORDER BY id DESC ".
          "LIMIT $offset, $rowsPerPage";

// ... the rest of the code
?>

First we set how many entries we want to show per page ( $rowsPerPage ). We will use this value with the LIMIT keyword in our query so the query will only get a chunk of all entries available.

The logic flow is like this. When the page is first loaded the $_GET['page'] is not yet initialized so we use the default $pageNum = 1. We then use $pageNum to count the offset ( the index of the first result we want to show ).

As an example, if $pageNum = 1, $offset will be (1 - 1) * 10 = 0. Our limit query will be "LIMIT 0, 10". This will select the first ten entries from our guestbook table.

Another example . When $pageNum = 3, $offset = 20, limit query is "LIMIT 20, 10" which will select ten result starting from the 20th index

Now that we have the query ready we need to create the navigation link so our visitor can easily move from the first page to other pages. We simply print the page number as a hyperlink. So when a visitor click on a page number the script will show the entries for the specified page.

The code needed is shown below

<?php
// .... previous code

$query    = "SELECT COUNT(id) AS numrows FROM guestbook";
$result   = mysql_query($query) or die('Error, query failed');
$row      = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);
$nextLink = '';

if($maxPage > 1)
{
   $self = $_SERVER['PHP_SELF'];

   $nextLink = array();

   for($page = 1; $page <= $maxPage; $page++)
   {
      $nextLink[] = "<a href=\"$self?page=$page\">$page</a>";
   }

   $nextLink = "Go to page : " . implode(' » ', $nextLink);
}

include 'library/closedb.php';
?>
<table width="550" border="0" cellpadding="2" cellspacing="0">
<tr>
<td align="right" class="text">
<?=$nextLink;?>
</td>
</tr>
</table>
<?php
}
?>

First we count the total number of entries we have ( $numrows ) then we find the maximum page numbers. To do this we just need the ceil() function to round the number up.

For example, suppose we have 34 entries in our guestbook database and we want to show 10 entries per page. From these numbers we know that we wil split the result in ceil( 34 / 10) = 4 pages.

If the entries span in more than one page we do a loop to create the links. The link will look something like this :

guestbook.php?page=3

Note that in above code we use $_SERVER['PHP_SELF'] instead of using the filename itself, guestbook.php. This is done to save the trouble of modifying the code if someday we want to change the filename.

We temporarily put the links in an array, $nextLink. Once we get all the links in there we just join them all using implode(). And now our guestbook is done. Congratulations to you :-).

If you want the source code for this guestbook tutorial just click here . The zip file contain all the files required but dont' forget to modify library/config.php to match your own settings.

Room For Improvements

Our guestbook script is actually very simple. You can really make lots of improvements, suc as :

Flood prevention
Prevent the visitor from signing the guestbook over and over again. You can log the visitor's IP and before saving the entry check the database if there's already an entry from such IP in the past hour ( or minute ). You can also use cookie for this
Bad words filtering
Before saving the message strip out any bad words. You can create an array listing the words you want to omit and then check the message against the list
Message size limitation
This is to prevent the visitor to enter a very long message. Spammers usually do this. Advertising their website in guestbooks.
Emoticons
You simply need to replace some special set of characters like :) or :( into an image tag. For example changing :) into <img src="emoticons/smile.gif">
Mail notification of new entry
Just use the mail() function after saving the message
Allow a specific set of HTML tags
This also can be achieved by simply searching and replacing unwanted HTML tags.