You've seen it at least once right? Guestbook is one of the most common thing to find in a website. In this tutorial we'll create a guestbook using PHP and MySQL.

I have split this tutorial into two section, each covering a specific feature of the guestbook.

Creating The Sign-Guestbook Form
This part will cover creating the database tables, the guestbook form and the process of saving the entry to database


Viewing The Entries
You want to see the guestbook entries of course. This section covers fetching the entries from database and put int into an HTML table. You will also learn to show the entries in multiple pages using MySQL paging.


 
I think you should take a quick look what the finished guestbook look like. Just click here to see it.

 

Creating The Sign-Guestbook Form
We start by creating the table to store the data, guestbook. There are six fields in the guestbook table:

1. id : the unique identifier for an entry in the guestbook
2. name : the visitor's name
3. email : visitor's email address
4. url : visitor's website url, if she has one
5. message : the message
6. entry_date : when did this entry added

 

I have put the SQL query needed to create the table in guestbook.txt.

Below is the HTML form code. It's pretty simple, we have text box for name, email and url plus a textarea to hold the message. The submit button is attached with a javascript function because we want to check the input values before the page is submitted.

Example :guestbook.php
Source code : guestbook.phps, guestbook.txt

<form method="post" name="guestform">
<table width="550" border="0" cellpadding="2" cellspacing="1">
<tr>
<td width="100">Name *</td> <td>
<input name="txtName" type="text" size="30" maxlength="30"></td>
</tr>
<tr>
<td width="100">Email</td>
<td>
<input name="txtEmail" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
<td width="100">Website URL</td>
<td>
<input name="txtUrl" type="text" value="http://" size="30" maxlength="50"></td>
</tr>
<tr>
<td width="100">Message *</td> <td>
<textarea name="mtxMessage" cols="80" rows="5"></textarea></td>
</tr>
<tr>
<td width="100">&nbsp;</td>
<td>
<input name="btnSign" type="submit" value="Sign Guestbook" onClick="return checkForm();"></td>
</tr>
</table>
</form>

Below is the javascript code to check the input form. The checkForm() function is called when the "Sign Guestbook" button is clicked.
 
The mandatory fields are name and message so if either is empty we pop an alert box to tell the visitor to enter the name and message. Email is not a mandatory field so we only check if in an email address is entered but we won't complain if there's none .

function checkForm()
{
   // the variables below are assigned to each
   // form input
   var gname, gemail, gurl, gmessage;

   with(window.document.guestform)
   {
      gname    = txtName;
      gemail   = txtEmail;
      gurl     = txtUrl;
      gmessage = mtxMessage;
   }

   // if name is empty alert the visitor
   if(trim(gname.value) == '')
   {
      alert('Please enter your name');
      gname.focus();
      return false;
   }
   // alert the visitor if email is empty or
   // if the format is not correct
   else if(trim(gemail.value) != '' && !isEmail(trim(gemail.value)))
   {
      alert('Please enter a valid email address or leave it blank');
      gemail.focus();
      return false;
   }
   // alert the visitor if message is empty
   else if(trim(gmessage.value) == '')
   {
      alert('Please enter your message');
      gmessage.focus();
      return false;
   }
   else
   {
      // when all input are correct
      // return true so the form will submit
      return true;
   }
}

/*
Strip whitespace from the beginning and end of a string
*/
function trim(str)
{
   return str.replace(/^\s+|\s+$/g,'');
}

/*
   Check if a string is in valid email format.
*/
function isEmail(str)
{
var regex = /^[-_.a-z0-9]+@(([-a-z0-9]+\.)+(ad|ae|aero|af|ag|
ai|al|am|an|ao|aq|ar|arpa|as|at|au|aw|az|ba|bb|bd|be|bf|bg|bh|
bi|biz|bj|bm|bn|bo|br|bs|bt|bv|bw|by|bz|ca|cc|cd|cf|cg|ch|ci|
ck|cl|cm|cn|co|com|coop|cr|cs|cu|cv|cx|cy|cz|de|dj|dk|dm|do|dz|
ec|edu|ee|eg|eh|er|es|et|eu|fi|fj|fk|fm|fo|fr|ga|gb|gd|ge|gf|gh|
gi|gl|gm|gn|gov|gp|gq|gr|gs|gt|gu|gw|gy|hk|hm|hn|hr|ht|hu|id|ie|
il|in|info|int|io|iq|ir|is|it|jm|jo|jp|ke|kg|kh|ki|km|kn|kp|kr|
kw|ky|kz|la|lb|lc|li|lk|lr|ls|lt|lu|lv|ly|ma|mc|md|mg|mh|mil|mk|
ml|mm|mn|mo|mp|mq|mr|ms|mt|mu|museum|mv|mw|mx|my|mz|na|name|nc|
ne|net|nf|ng|ni|nl|no|np|nr|nt|nu|nz|om|org|pa|pe|pf|pg|ph|pk|
pl|pm|pn|pr|pro|ps|pt|pw|py|qa|re|ro|ru|rw|sa|sb|sc|sd|se|sg|sh|
si|sj|sk|sl|sm|sn|so|sr|st|su|sv|sy|sz|tc|td|tf|tg|th|tj|tk|tm|
tn|to|tp|tr|tt|tv|tw|tz|ua|ug|uk|um|us|uy|uz|va|vc|ve|vg|vi|vn|
vu|wf|ws|ye|yt|yu|za|zm|zw)|(([0-9][0-9]?|[0-1][0-9][0-9]|[2]
[0-4][0-9]|[2][5][0-5])\.){3}([0-9][0-9]?|[0-1][0-9][0-9]|[2]
[0-4][0-9]|[2][5][0-5]))$/i;
return regex.test(str);
}

 
 
After the form is submitted our job turns to saving the input into the database.

In the code below I include config.php and opendb.php which contain the database configuration and the code needed to open a connection to MySQL. It's a good practice to put these actions in separate file. That way everytime you need to connect to MySQL you can include these files instead of rewriting the code. Also you can change the database information from just one file instead of changing it in every file that use MySQL. To see what the content of config.php, opendb.php and closedb.php go to : Connecting to MySQL database

<?php

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

if(isset($_POST['btnSign']))
{
   include 'library/config.php';
   include 'library/opendb.php';


   $name    = trim($_POST['txtName']);
   $email   = trim($_POST['txtEmail']);
   $url     = trim($_POST['txtUrl']);
   $message = trim($_POST['mtxMessage']);

   if(!get_magic_quotes_gpc())
   {
      $message = addslashes($message);
   }


   // if the visitor do not enter the url
   // set $url to an empty string
   if ($url == 'http://')
   {
      $url = '';
   }

   $query = "INSERT INTO guestbook (name,
                                    email,
                                    url,
                                    message,
                                    entry_date)
             VALUES ('$name',
                     '$email',
                     '$url',
                     '$message',
                     current_date)";

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

   header('Location: ' . $_SERVER['REQUEST_URI']);
   exit;
}
?>

The script check if the $_POST['btnSign'] variable is set. If it is then the "Sign Guestbook" button must have been clicked and now we can read name, email, url and message from the $_POST global variable. After that we create an INSERT query string and execute the query using mysql_query().

Sometimes a message can contain single quotes, we need to escape these single quotes ( replacing it with \' ) otherwise MySQL will think that it's the end of a string and the query will fail. We use the addslashes() function to escape the string.

Unfortunately some web hosts set the magic_quotes_gpc setting on. This will make values containing single-quotes in $_GET, $_POST and $_COOKIE will be automatically escaped. If we use addslashes() when the string is already escaped the result would be a mess.

To check if magic_quotes_gpc is On use get_magic_quotes_gpc(). If it returns true then we don't have to call addslashes().

Ok, now affter all input is ready we can build the query string to enter the name, email, url, message and entry date. Note that for the entry_date field we use current_date. This is not a PHP variable or function, it's a built in MySQL function that returns ( guess what? ) the current date.

You also see that I didn't explicitly insert the value of id field. This is because id is set as auto_increment so when we insert a new row into the table a new value for id is automatically generated ( incremented for each new row).

After inserting the new guestbook entry the next thing we do is redirect back to current page using header('Location: ' . $_SERVER['REQUEST_URI']);

Why?

The redirect is just to prevent double submission. Suppose we don't use the redirect and the visitor hit the refresh button after signing up the guestbook then the form will be submitted again.

Note : If you get this kind of error message

Warning: Cannot modify header information - headers already sent by (output started at C:\webroot\guestbook\library\config.php:7) in C:\webroot\guestbook\guestbook.php on line 43

this mean the redirect failed because you already sent something to the browser. I got the error message above because i "accidentally" have a space right after the closing tag ( ?> ) in config.php. By removing this space the error is fixed.

This kind of errror is actually very common to see when your code is sending headers and fixing it is easy like the example above. Just check the file pointed by the error message and see if you accidentally sent ( print ) anyhing to the browser.


--------------------------------------------------------------------------------

Pheww, we just finished the first part of our guestbook. Now it's time to create the script which will show the guestbook entries. We'll also try to split the entries into multiple pages when they are too much to be shown in one page.
 
Source: http://www.php-mysql-tutorial....stbook.php

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
Example : guestbook.php
Source code :guestbook.phps

<?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>

<!-- this is where we put the guestbook form -->

<?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 &lt;b&gt;I am a wizard&lt;/b&gt;

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 :

<script>
while(true)
{
   window.open("http://www.google.com");
}
</script>
  
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
Example : guestbook.php
Source code :guestbook.phps

<?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(' &raquo; ', $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.


Source: http://www.php-mysql-tutorial....book-2.php