Writing a simple database driven guestbook

by Pandafox | Posted in PHP, Tutorials

Guestbooks aren’t as popular as they once were, but writing one is still a good way to learn some PHP and SQL

You don’t need much experience with MySQL, or even PHP to get through this tutorial, but you will, of course, need a server with MySQL and PHP.

We’re only going to write three small php-scripts and you can go ahead and set them up now, before we start:

  1. connect.php, which will set up a connection to the database.
  2. guestbook.php, which will show all the guestbook entries, along with a form.
  3. post.php, which will save new entries into our database.

Ok, so let’s get going!

The database table

To store our guestbook entries we will need to set up a new table in our database. We’re only going to be storing nicknames and messages so our SQL table creation statement should be simple enough. If you happen to have a tool like PHPMyAdmin, you can by all means go ahead and use that, but for learning purposes you may want to have a closer look at some raw SQL code:

use mydatabase;
CREATE TABLE guestbook (
    nickname VARCHAR(32),
    message TEXT
);

The first thing we do in this code is to specify which database we want to use. You should replace “mydatabase” with the name of your own database. Then comes our create table statement where we tell the database that we want our new table to be called guestbook and that it should have two columns (nickname and message). In the nickname column, we want to be able to store strings, and we don’t want any nicknames to exceed 32 characters. We therefore use set the nickname type to VARCHAR (which can hold up to 256 characters) and we limit its size to 32 characters. We want the message column to store text that is longer than 256 characters, so we set the type to TEXT which can pretty much store an unlimited number of characters.

Go ahead and run this statement in your favorite MySQL client and you should be good to go.

The Connection

For the PHP portion of this system we will start with connect.php. This script will simply establish a connection to our database. Since both our other scripts will require a database connection, we put the connection code in a file of its own, which can be included wherever a connection is needed. Thus, saving us some precious time.

<?php
$connection = mysql_connect('localhost', 'username', 'password');
mysql_select_db('database', $connection);

We create and store our connection link in a variable called $connection by calling mysql_connect which takes host, username and password as its arguments. Host is usually localhost if the mysql server is on the same server as the http server, but it’s could also be something like mysql21.example.com etc.

You will of course have to replace this login information with your own.

mysql_select_db makes our lives a little bit easier. By calling this function, a default database will be set so we wont have to specify which database to use everytime we execute a query. You should replace ‘database’ with the name of your own database.

And that’s it for our connection script!

The Form

Ok, it’s time to write some HTML!

We want our users to be able to fill in their nickname and a message into a simple form. This data will be sent over to our server once the user hits the submit-button. We can set up a form like this in HTML by using the form, input and textarea tags like this:

<html>
<head>
    <title>My Fantastic Guestbook</title>
</head>
<body>
<form method="post" action="post.php">
    <label for="nickname" maxlength="16">Nickname</label>
    <br />
    <input type="text" name="nickname" id="nickname" />
    <br />
    <label for="message">Message</label>
    <br />
    <textarea name="message" id="message"></textarea>
    <br />
    <input type="submit" value="Post Message" />
</form>
</body>
</html>

In this piece of code we have three input elements inside a form-tag. Our first input element is of the type “text” which is a simple 1-line text field. We give the field a name, which we will be needing later when we have to collect all the submitted data. We also give the elements id’s because we want to associate the fields with its labels (making the lables clickable). We also set maxlength to 16, which will, indeed, limit the input to 16 characters (This isn’t 100% secure though. We will still have to check the size later, using PHP)

The textarea works pretty much like the text input field, but it will be possible to type in more than one line. The textarea is also given an id and a label.

And finally, we have a submit element, which is basically just a button that submits the form it is nested inside. When the submit button is clicked, the browser will collect all the input data from the form and send it along to the page which is specified in the forms action attribute. In this case, this will be “post.php”, which is the page we will be writing next.

The “method” argument, in the form tag, specifies what kind of HTTP-request should be used. We will be using “post” which can be used to transfer larger amounts of data.

The Handling

When the user submits a message, it will be sent to post.php, which we will be working with now. To avoid security breaches, we will have to check and secure the submitted data before we try to store it in our database. This is really, really important, as this can easily lead to one of the most common security holes; SQL-injections.

But let’s start by collecting the input data (in post.php):

<?php
$nickname = $_POST['nickname'];
$message = $_POST['message'];

The input data we need to collect is simply stored in the $_POST array, and the field names we assigned in our form is used as keys. We now have two variables with untreated input data (nickname and message). The next step is to validate and secure them.

There are several things we have to worry about, like:

  • HTML-injections / XSSWe don’t want people to be able to post HTML and JavaScript code which can cause us any harm.
  • SQL-injectionsWe don’t want people to execute SQL-code on our server by entering it into one of our fields.
  • SizeWe want to set minimum and maximum lengths for both nickname and message.

If the data isnt valid, we will simply stop everything and display a simple message (by calling die(‘some message’)). In reality you would want to something more, but we wont go into that in this tutorial.

Let’s start by eliminating the possibilites for SQL-injections:

$nickname = mysql_real_escape_string($nickname);
$message = mysql_real_escape_string($message);

mysql_real_escape_string escapes any quotes and strips certain special characters in a given string. Any ” or ‘ characters will turn into \’ and \” which will be treated as regular characters in SQL and thus rendering them harmless.

To get rid of HTML-injections, we could have used htmlspecialchars  which turns special characters into html entities. e.g. < turns into &lt; The problem with this is that the strings can get longer and we want to keep them as short as possible when we’re storing them in our database. So we’re just going to use htmlspecialchars  when we output our messages on our site (We’ll get back to that later).

We also want to limit the length of the nicknames and messages and we can accomplish this by using a cute little function called strlen, some if-statements and the nasty die function like so:

if (strlen($nickname) > 16 || strlen($nickname) < 3)
{
     die('Sorry, your nickname has to be between 3 and 16 characters.');
}
if (strlen($message) > 512 || strlen($message) < 32)
{
     die('Sorry, your message has to be between 32 and 512 characters.');
}

As you probably know by now, strlen (short for string length) returns the length (as in number of characters) of a given string. We use the less than and greater than operators along with the || (Logical OR) operator to check if the nickname or message length is out of bounds. If it is, the die function will get called and the script execution will be terminated, printing out nothing but a short little message which is passed to the die function as it’s argument.

Ok, now it’s time to insert the new data into our database:

if (!mysql_query(
    "insert into guestbook (nickname, message) VALUES ('$nickname', '$message')"
))
{
     die('Sorry, a database error occurred. Your message was not saved.');
}

mysql_query executes our query and returns false if an error occurs. In this case we simply kill our script with the die function if something goes wrong.

If your script is still running at this point, then the messages has been added to our database and all that is left for us to do is to print out a little message for the user:

echo 'Your message was successfully added.';

You could should of course leave a longer message than that and maybe even redirect the user back to the main page, but we won’t go into that in this tutorial.

Finishing up guestbook.php

The only thing left for us to do is to display the guestbook entries on our site. So go ahead and fire up the file containing our form, guesbook.php, and then add the following code wherever you want the entries to show up. (Before or after <form> ... </form>)

<?php
$query = mysql_query('select * from guestbook');
while ($entry = mysql_fetch_object($query))
{
    printf("<p>%s <br />- <em>%s</em></p>",
        htmlentities($entry->message),
        htmlentities($entry->nickname)
    );
}
?>

In this piece of code we get to meet our old friend mysql_query again, but this time we use it to get existing data from the database rather than inserting new data. The SQL query is simple enough; We just select * (all the columns) from our guestbook. If the query is successful, $query will contain a result set with all our guestbook entries.

To extract our entry data from the result set, we use the mysql_fetch_object function which fetches and removes an item from the result set. If the result set is empty, the function will return false and thus ending our while-loop. Each entry gets stored in $entry which we use inside our loop to print out the messages.

We use printf (print formatted) to print out our entries, just to keep our code a bit cleaner. The first argument in printf is the format we want the output to be in and the rest of the arguments is the data we want to display in the given format. In this case the first %s (which tells printf that the values should be displayed as a string) in our format will be replaced with our message, and the second %s will be replaced with our nickname.

When inserting the nickname and message into our database, we didn’t strip any potential HTML-code and that is why we run both nickname and message through htmlentities before outputting them. htmlentities renders any html tags hamless by replacing characters such as < with &lt; and so on.

And that’s pretty much it! Our guestbook is done and you’ve hopefully learned a thing or two.

But what’s next? Well, here are some things you may want to add:

  • Pagination
  • Emoticon support
  • Prettier user interface
  • Timestamps
  • A captcha system to prevent automated spamming

The possibilities are endless, so have fun!