Do you want to encourage your visitors to say hi when they visit your friendly Website? This tutorial is divided in 4 phases and will teach you how to create a simple PHP guestbook using only one PHP file.

We will create the PHP/MySQL Guestbook in four phases

1. Setting up the database
2. Creating the Guestbook form
3. Coding the PHP script that will process the guestbook submissions
4. Coding the PHP script that will display the guestbook entries

1. Setting up the database

The table’s name will be guestbook. We are going to setup the following columns:

• id – the unique identifier for a guestbook entry
• name – the visitor’s name
• email – the visitor’s email
• comments – the message
• datetimecreated – to record the date and time the visitor submitted an entry

If you haven’t created a database yet, using whatever interface you use for working with MySQL databases, you can execute the following command to create a database.

CREATE DATABASE mywebsite;

You can execute the following command to create our guestbook table.

CREATE TABLE guestbook
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name TEXT,
    email TEXT,
    comments TEXT,
    datetimecreated DATETIME NOT NULL
);

2. Creating the Guestbook form

We have 3 fields where the visitor can enter data and 1 hidden field with a preset value:

• name: for the user to input his/her name
• email: for the user to input his/her email address
• comments: guestbook comments

process: is a hidden fields that will send a value of 1 when the user clicks the Send Message! button. We use this hidden field to let the PHP code know that the user has clicked on the button and that it should process the code that inserts the data in the database

When the user clicks the Send Message! button, a $_POST array is created. $_POST is an array of variable names and values sent by the HTTP POST method. The $_POST variable is used to collect values from a form that uses the POST method.

3. Coding the PHP script that will process the guestbook submissions

if(isset($_POST['process']) and $_POST['process']==1) {
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $comments = trim($_POST['comments']);

    $query = 'INSERT INTO guestbook SET
         name = "'.$name.'",
         email = "'.$email.'",
         comments = "'.$comments.'",
         datetimecreated = NOW()';

    if(mysql_query($query)) {
        echo '
Thank you for signing our guestbook!

';
    }
    else {
        echo '
Sorry, your entry could not be submitted.

';
    }
}

if(isset($_POST['process']) and $_POST['process']==1) allows us to process the code inside the if statement ONLY when the submit button has been clicked.

We build the query using the values entered into the form and the NOW() MySQL function which returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ format.

The msyql_query() function returns TRUE if the email was successfully sent, FALSE otherwise. If the function was executed successfully, a thank you message in blue letters will be displayed. If the function returns a FALSE value (the query was not successful), the code echos an error message in red letters.

4. Coding the PHP script that will display the guestbook entries

$query = 'SELECT * FROM guestbook ORDER BY datetimecreated DESC';
$result = mysql_query($query);
if(!$result) {
    echo '
Could not retrieve guestbook entries.

';
}
else {
    while($row = mysql_fetch_array($result)) {
        echo '

';
        echo ''.$row['name'].'';
        echo $row['email'].'';
        echo date('F j, Y', strtotime($row['datetimecreated'])).' at '.date('g:i a', strtotime($row['datetimecreated'])).'';
        echo $row['comments'];
        echo '

';
    }
}

The query will sort the results from newest to oldest by including the ORDER BY clause.

For SELECT statements, mysql_query returns a resource on success, or FALSE on error.
In our script, !$result will equal to TRUE on error. This is because of the way the ! logical operator works. For example, !$result is TRUE if $result is not TRUE. We could translate it as “if $result is not TRUE”. Consequently, if !$result is FALSE, the if statement evaluates to TRUE and an error message in red letters will be displayed.

If mysql_query is successfully executed the while loop will display the text of each entry in a paragraph.

There we go! With a single file we are able to add and view entries to our guestbook.

TIPS
When accepting data from forms you should always validate the data and be protected against SQL injection attacks. If you don’t know what a SQL injection attack is and how to protect from them, it is imperative you get informed about this important subject. Don’t risk the integrity of your databases. Please read Protect your script from hackers: SQL injection attack prevention.

Tags: MySQL, PHP

This entry was posted on Thursday, January 14th, 2010 at 11:07 am and is filed under Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.