Php & MySQL Guestbook tutorial – Part 1

A few weeks ago, Reg asked me to add a guestbook to his site. ‘Sure’, I said, ‘no problem’, and began scouring the web for a nice, clean, simple, XHTML strict guestbook script. Could I find one? There was more chance of me finding the source code for vista (that’s another story though!)

So I quickly realised that the only way Reg was going to have a guestbook on his site was if I wrote it. So I did. And here’s a quick tutorial on the whole process.

You should equip yourself thus:

• local server – I recommend xampp
• text editor (do not use MS word, works, openoffice etc)
• browser – the one you’re using will do fine.
• an extremely large mug of tea or coffee.

… if you’re using lynx you might want to try a GUI at some point!

//Guestbook Tutorial Part 1 begins

Ok, the first thing we need to do is to set up the database tables for the guestbook. I set mine up in phpmyadmin, but you could use php to do it like this:
NB you will need to set up a database first – phpmyadmin makes this very simple.

<?php
//connect to db
$cxn=mysql_connect("localhost","username","password");
if(!$cxn){
 die("Database connection failed: ".mysql_error());
}
$dbcxn=mysql_select_db(database_name,$cxn);
if(!$dbcxn){
 die("Database selection failed: ".mysql_error());
?>

Ok, hopefully this bit is obvious – php connects to MySQL, or outputs an error message. Then php connects to your database, or gives an error as before. We’ll come back to this snippet later as an include file for the main script, but for now, we’ll need some database action!

The guestbook needs two tables in the database: a logins table where username and password of admins is specified, and a guestbook table where the guestbook comments are stored.

Using the code above as a starting point, add the following to the file, save it as database_setup.php and point your browser to it (only do this ONCE!)

<?php
//create the login table
mysql_query("CREATE TABLE login(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30),
password VARCHAR(50)")
or die("Unable to create table: login ".mysql_error());

echo"login table created</br>";

//create the guestbook table
mysql_query("CREATE TABLE guestbook(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
email VARCHAR(30),
web VARCHAR(50),
message TEXT")
or die("Unable to create table: guestbook ".mysql_error());

echo"guestbook table created";
?>

Ok, so if all went well, you should have seen this in your browser:

login table created
guestbook table created

Once this is done, you’re no longer going to need this file, so you can delete it. Bear in mind you’re going to re-use the first bit of code, so maybe just save it to a directory outside of your sandbox.

//Create the Guestbook page

First things first. Each file in this wee program will want to query the database, so instead of repeating the same bit of code in eash file, we can simply create a single file with the information we need, and then ask php to fetch it for us from each file.

Type the following into your text editor, and save it as connect.php

<?php
//connect to db
$cxn=mysql_connect("localhost","username","password");
if(!$cxn){
 die("Database connection failed: ".mysql_error());
}
$dbcxn=mysql_select_db(database_name,$cxn);
if(!$dbcxn){
 die("Database selection failed: ".mysql_error());
?>

Good. Now we can begin the guestbook page. First thing we need to do is talk to the database to make sure we can retrieve the information.

<?php include("connect.php"); ?>

Simple as signing on, right?

Next we need to present the user with a form they can use to sign the guestbook, along with the guestbook entries that have already been submitted by other users. Sounds easy enough, and it is really, but the order in which the code appears on the page may not make perfect sense at first glance. Don’t worry about this. Php reads all the code in your script before executing it, so the order is important in different ways than you may think!

Ok, here’s the form we’re going to use. Simple, clean, XHTML. Tasty!

<div id="signbook">
<form method="post" action="<? echo $PHP_SELF;?>">
<fieldset>
<legend>Sign the Guestbook</legend>
<?echo'<p class="error">'.$error.'</p>'?>
<ul>
<li><label for="name"><span class="asterix">*</span>
Name</label><input type="text" name="name"></li>
<li><label for="email"><span class="asterix">*</span>
Email (will not be published)</label>
<input type="text" name="email"></li>
<li><label for="website">&nbsp;Website</label>
<input type="text" name="website"></li>
<li><label for="comments"><span class="asterix">*</span>
Comments</label><textarea name="comments"></textarea></li>
<li><label for="captcha"><span class="asterix">*</span>
What colour is the sky?</label>
<input type="text" name="captcha"></li>
<li><label for="submit"></label>
<input type="submit" value="Sign Guestbook"></li>
</ul>
</fieldset>
</form>
</div>	<!-- end div signbook -->

You’ll have noticed there are 2 bits of php in there. The first one echo $PHP_SELF basically tells php that when the submit button is pressed, to run the code on this page. Don’t worry about the other bit for now, I’ll reveal what’s going on with it in a bit. You’ll also notice that I’ve included a super accessible captcha. Are you sick of trying to decipher those awful image based captchas every time you interact with a web form? Me too – here’s a nice clean and simple solution that’s easy to code, too.

Next, we want to display the existing messages in the guestbook. Here’s the code:

<?php
//get results from database
$query="SELECT * FROM guestbook";
$result=mysql_query($query)or die(mysql_error());

//list messages
while($row=mysql_fetch_array($result)){
 echo '<p class="name">';
 if ($row['web']!='')
 {
 echo '<a href="';
 echo $row['web'];
 echo '">';
 echo $row['name'];
 echo '</a> says:</p>';
 }
 else {
 echo '<span class="name">'.$row['name'].'</span>';
 echo ' says:</p>';
 }
 echo '<p class="message">';
 echo $row['message'];
 echo '</p>';
}
?>

Looks complicated doesn’t it? It’s not really. Here’s how it works.

$query="SELECT * FROM guestbook";
$result=mysql_query($query)or die(mysql_error());

This simply asks php to select all the information from the guestbook table, and assign it to a variable called $result, or print an error message.

while($row=mysql_fetch_array($result))

This tells php to read the code that follows this statement in curly braces {} and run it while (or each time) it finds a row in the table. The mysql_fetch_array($result) funtion in this statement assigns the information in a single row from the guestbook table into an array.

echo '<p class="name">';
 if ($row['web']!='')
 {
 echo '<a href="';
 echo $row['web'];
 echo '">';
 echo $row['name'];
 echo '</a> says:</p>';
 }
 else {
 echo '<span class="name">'.$row['name'].'</span>';
 echo ' says:</p>';
 }
 echo '<p class="message">';
 echo $row['message'];
 echo '</p>';

This is where php outputs the messages from the database. Basically it says, if the user has submitted a web address to use it to link to their name, otherwise, to simply print the name, and the message. Simple eh? The email address is stored in the database but not made public. Administrators will be able to view them, but we’ll come to that later.

Right, now we need to tell php how to process the information from the form, and save it to the database. There are a few steps involved.

//assign form data to vars, protect from sql injection
$name=$_POST['name'];
$email=$_POST['email'];
$website=$_POST['website'];
$comments=$_POST['comments'];
$captcha=$_POST['captcha'];
$name=stripslashes($name);
$email=stripslashes($email);
$website=stripslashes($website);
$comments=stripslashes($comments);
$captcha=stripslashes($captcha);
$name=mysql_real_escape_string($name);
$email=mysql_real_escape_string($email);
$website=mysql_real_escape_string($website);
$comments=mysql_real_escape_string($comments);
$captcha=mysql_real_escape_string($captcha);
$captcha=strtolower($captcha);

This section saves any information the user submits into variables, and while it’s at it, uses functions such as stripslashes() and mysql_real_escape_string() to prevent any sql injections by rogues, terrorists and other deviants!
It also converts the answer to the captcha to lower case, thus making it easier to process.

The next bit validates the form.

//create new db entry
if (!$_POST){
 $error='Required fields are marked *';
 }
 elseif(trim($name) == '')
 {
 $error='Please enter your name';
 }
 elseif(trim($email) == '')
 {
 $error='Please enter a valid email address';
 }
 elseif(trim($comments)=='')
 {
 $error='Please type your message';
 }
 elseif(trim($captcha)!='blue')
 {
 $error='You did not answer the question correctly';
 }

The most important bit here is the first line (no, not the comment, the code, silly!)

if (!$_POST)

You notice the ! in there? Basically the ! means not. As in, my own personal abbreviation: !b. The code is saying, if there is no information posted, do the following.
If you’ve been paying attention here, you will have noticed that this section is incomplete. What we have so far is form validation, preparation for the data to be saved to the database. Here’s the magic beans:

//if all data is present and captcha is correct, insert data into db
 elseif ($captcha=="blue") {
 mysql_query("INSERT INTO guestbook
 (name,email,web,message) VALUES ('$name','$email','$website','$comments')")
 or die(mysql_error());
 $error="Your message has been published";
}

Yes, you’re reading that correctly. I have used a confirmation statement and called it an error. It’s just a shortcut really, maybe I should have used a different variable name for $error, but my imagination was staging a protest at the time.

And that’s the guestbook script. Here it is in full, in case you don’t like it all chopped up like that. In part 2, the login script, and the admin page. Watch this space!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" >
<head>
<title>Guestbook</title>
</head>
<body>
<?php
include("connect.php");

//assign form data to vars, protect from sql injection
$name=$_POST['name'];
$email=$_POST['email'];
$website=$_POST['website'];
$comments=$_POST['comments'];
$captcha=$_POST['captcha'];
$name=stripslashes($name);
$email=stripslashes($email);
$website=stripslashes($website);
$comments=stripslashes($comments);
$captcha=stripslashes($captcha);
$name=mysql_real_escape_string($name);
$email=mysql_real_escape_string($email);
$website=mysql_real_escape_string($website);
$comments=mysql_real_escape_string($comments);
$captcha=mysql_real_escape_string($captcha);
$captcha=strtolower($captcha);

//create new db entry
if (!$_POST){
 $error='Required fields are marked *';
 }
 elseif(trim($name) == '')
 {
 $error='Please enter your name';
 }
 elseif(trim($email) == '')
 {
 $error='Please enter a valid email address';
 }
 elseif(trim($comments)=='')
 {
 $error='Please type your message';
 }
 elseif(trim($captcha)!='blue')
 {
 $error='You did not answer the question correctly';
 }
//if all data is present and captcha is correct, insert data into db
 elseif ($captcha=="blue") {
 mysql_query("INSERT INTO guestbook
 (name,email,web,message) VALUES ('$name','$email','$website','$comments')")
 or die(mysql_error());
 $error="Your message has been published";
}

?>
<div id="signbook">
<form method="post" action="<? echo $PHP_SELF;?>">
<fieldset>
<legend>Sign the Guestbook</legend>
<?echo'<p class="error">'.$error.'</p>'?>
<ul>
<li><label for="name"><span class="asterix">*</span>Name</label>
<input type="text" name="name"></li>
<li><label for="email"><span class="asterix">*</span>
Email (will not be published)</label>
<input type="text" name="email"></li>
<li><label for="website">&nbsp;Website</label>
<input type="text" name="website"></li>
<li><label for="comments"><span class="asterix">*</span>
Comments</label><textarea name="comments"></textarea></li>
<li><label for="captcha"><span class="asterix">*</span>What
colour is the sky?</label><input type="text" name="captcha"></li>
<li><label for="submit"></label>
<input type="submit" value="Sign Guestbook"></li>
</ul>
</fieldset>
</form>
</div>	<!-- end div signbook -->
<?php
//get results from database
$query="SELECT * FROM guestbook";
$result=mysql_query($query)or die(mysql_error());

//list messages
while($row=mysql_fetch_array($result)){
 echo '<p class="name">';
 if ($row['web']!='')
 {
 echo '<a href="';
 echo $row['web'];
 echo '">';
 echo $row['name'];
 echo '</a> says:</p>';
 }
 else {
 echo '<span class="name">'.$row['name'].'</span>';
 echo ' says:</p>';
 }
 echo '<p class="message">';
 echo $row['message'];
 echo '</p>';
}

?>
</body>
</html>

Filed under: php , guestbook, mysql, tutorial