[php] Guestbook

[Klas2B 0]

This tutorial will run through creating a very simple PHP and MySQL based guestbook, from creating the database table to storing and displaying the messages and assumes that you have either phpmyadmin or some other database manager available. phpmyadmin can be downloaded from https://www.phpmyadmin.net/
Structure

When you create a table in MySQL you need to decide what information you're going to store there and how many columns you'll need. For a simple guestbook you would probally want a column for each of the following: name, email, message, date posted.

The first thing you need is a CREATE TABLE statement, then you also need to give the table a name, we'll call it 'guestbook'

Now it's useful to give each of the guestbook entries an id number, so that later we can refer to the id if we want to edit or delete certain posts, so the next line for creating our table could be something like: id INT(10) not null AUTO_INCREMENT

This is basically creating a field of integer type with a width of 10 and the 'auto_increment' will mean each entry has a unique id making them easier to refer to later.

Now onto the fields for the entries into the guestbook:

* Name - the person posting the message, we'll use name VARCHAR(50) this gives us a field that can have a varying number of characters with a maximum length of 50.
* Email - the email of the person posting the message email VARCHAR(50) same as above.
* Message - the actual message the person is posting, for this we'll use message TEXT a text field as we don't want the same restrictions on the message as we have on the name and email.
* Date - the time and date the message was posted date CHAR(10) we'll be using a UNIX time value which is 10 characters in length. (It'll be 11 characters in about 35 years time, so i don't think we really need to allow for that =])

Finally, we'll add the following PRIMARY KEY(id) a primary key is a unique key where each entry must be defined as not null, this is the main key that MySQL will use when refering to entries in the table.
Put It Together

Now that we have everything ready to create the table we need, all that's left is to put it together:

CREATE TABLE guestbook(            id INT(10) not null AUTO_INCREMENT,            name VARCHAR(50),            email VARCHAR(50),            message TEXT,            date CHAR(10),            PRIMARY KEY(id));

Now that we have our table ready we can make a simple form to add the entries into the guestbook.

Often you'll find it easier to have a seperate page for your form that people fill out and for the code needed to add the data into your table, but i'll give an example of doing everything in one page because it's easier to explain that way.
Connecting

The first thing we need to do is check the form has been posted and connect to our database:

<?php
if($_POST['submit'])
{
$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());

Obviously you need to replace the database values (host, user, password and database name) with your own. The if($_POST['submit']) line means that the script will only connect once somebody submits the form, as there's no need to connect to the database everytime the page loads.

Now we can form the query that will add the information into our table.

$sql[addpost] = mysql_query("INSERT INTO guestbook (name, email, message, date)
VALUES ('$_POST[name]','$_POST[email]','$_POST[message]',time())");

mysql_close($db);

echo "Thanks ".$_POST['name']." your message has been added\n";
}

When we put it all together it should look something like this:

<?php
if($_POST['submit'])
{
$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[addpost] = mysql_query("INSERT INTO guestbook (name, email, message, date)
VALUES ('$_POST[name]','$_POST[email]','$_POST[message]',time())");

mysql_close($db);

echo "Thanks ".$_POST['name']." your message has been added\n";
}

?>

The time() function given for the date field returns a UNIX timestamp of the current time and date, later we'll convert this into a readable date format.
The Form

Now all that's really left is to create the form for the user to fill in.

I'm hoping that if you're reading about creating and submitting infomation to a database that you at least know how to create a basic form, so i wont explain this much.

<form method="post" action="./"><table cellpadding="6" cellspacing="0"> <tr>  <td>Name :</td>  <td><input type="text" name="name" /></td> </tr> <tr>  <td>Email :</td>  <td><input type="text" name="email" /></td> </tr> <tr>  <td valign="top">Message :</td>  <td><textarea name="message" cols="30" rows="6"></textarea></td> </tr> <tr>  <td> </td>  <td>  <input type="submit" name="submit" value="Add Message" />  <input type="reset" name="reset" value="Clear Message" />  </td> </tr></table></form>

We just have three inputs for our fields name, email and message.

That's all, aslong as you followed everything correctly when you submit the form, the details will be added to the database.

Now this is far from perfect, you would really want to firstly check that all the fields are completed before the form is submitted, otherwise you'll get people posting blank messages, check the query is successful before telling the user their message was posted and probally limit the amount of characters allowed in the name and message.

It would also be a good idea to strip out the slashes from peoples messages to stop them spamming your guestbook with urls or trying to post HTML code and Javascript but this is intended to cover only the very basics so that'll require some thought and research from you to put in place.

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.

Now you have your very own guestbook