Thursday, August 9, 2012

Guestbook in PHP and MySQL

Introduction

I'm going to create an example of guestbook system using in PHP and MySQL. On the Web, guestbook is a logging system that lets visitor post their message. A visitor can post their thoughts or expressions. In general, it's not a requirement that a visitor create an account.


The guestbook form includes four (4) fields – Name, Email Address, Website, and Message. All these fields are required except for Website; not all have their Website. If a guest entered his/her Website URL, the guest name will have a link (<a></a>) to his/her Website. Email Address and Website (if guest entered one), have each simple validation function to check if values entered are valid.

Unfortunately, this guestbook does not incorporate pagination feature. In due time, I'll create another post which may include as well as other special features a guestbook may have.

This tutorial contains seven (7) files:

- guestbook.php – the guestbook form
- addGuestbook.inc.php – processes the values entered in the guestbook form
- validateEmailAddress.inc.php – includes a function that checks the validity of Email Address entered in the form
- validateURL.inc.php – includes a function that checks the validity of URL or Website address entered in the form
- viewGuestbook.inc.php – displays all messages that have been successfully posted
- dbConfig.inc.php – database configuration
- style.css – stylesheet

Files addGuestbook.inc.php, dbConfig.inc.php, validateEmailAddress.inc.php, validateURL.inc.php, and viewGuestbook.inc.php should be placed under folder/directory 'includes', style.css under 'css' and the guestbook.php in the document root of your application:

- guestbook.php
- css/style.css
- includes/addGuestbook.inc.php
- includes/dbConfig.inc.php
- validateEmailAddress.inc.php
- validateURL.inc.php
- viewGuestbook.inc.php

The Database

Let us create database, in my case `mydb`; you can create one with different name but make sure you also update dbConfig.inc.php. The guestbook includes only one (1) table - guestbook. The table will include six (6) fields.

CREATE TABLE `guestbook` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`email_address` VARCHAR(132) NOT NULL,
`website` VARCHAR(100) NULL DEFAULT '',
`message` TEXT NOT NULL,
`date_created` DATETIME NOT NULL,
PRIMARY KEY (`id`)
);

The Codes

- guestbook.php

<?php
//guestbook.php
/*
 *
 * For God so loved the world that He gave His only begotten Son
 * that whosoever believes in Him shall not perish but have an everlasting life.
 * - John 3:16
 *
 * Author:      Julian V. Jupiter
 * Facebook:    facebook.com/julianjupiter
 * Twitter:     twitter.com/dealwithjulez
 * Website:     http://www.jupitersnotebook.co.cc
 */
?>
<?php include './includes/dbConfig.inc.php';?>
<?php include './includes/addGuestbook.inc.php';?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>PHP & MySQL Guestbook</title>
        <link rel="stylesheet" href="./css/style.css" />
    </head>
    <body>
        <div id="wrapper">
            <header>
                <hgroup>
                    <h3>PHP & MySQL Guestbook</h1>  
                    <h4>By: Jupiter's Notebook</h2>
                </hgroup>
            </header>
            <?php
            if(count($errors) > 0) {
                echo '<section class="message">';
                foreach ($errors as $error) {
                    echo $error.'<br />';
                }
                echo '</section>';
            }
            if($success != '') {
                echo '<section class="message">';
                echo $success.'<br />';
                echo '</section>';
            }            
            ?>
            <section>
                <form name="guestbookForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
                    <ul>
                        <li>
                            <label for="name">*Name:</label>
                            <input type="text" name="name" value="<?php echo $name;?>" size="25" autofocus />
                        </li>
                        <li>
                            <label for="emailAddress">*Email Address:</label>
                            <input type="text" name="emailAddress" value="<?php echo $emailAddress;?>" size="25" />
                        </li>
                        <li>
                            <label for="Website">Website:</label>
                            <input type="text" name="website" value="<?php echo $website;?>" size="25" />
                        </li>
                        <li>
                            <label>*Message:</label>
                            <textarea name="message" rows="4" cols="50"><?php echo $message;?>
                            </textarea>
                        </li>
                        <li>
                            <span>* Required fields</span>
                        </li>
                        <li>
                            <br />
                            <input type="submit" value="Submit" name="submit" id="button" />                            
                        </li>
                    </ul>
                </form>
            </section>
            <?php
            include './includes/viewGuestbook.inc.php';
            ?>
        </div>
        <footer>
            <p>&copy; 2012 <a href="http://www.jupitersnotebook.co.cc">Jupiter's Notebook</a></p>
        </footer>
    </body>
</html>

- style.css

/*
style.css
*/
body {
    background: #ccccff;
    color: #0000ff;
    font-family: Georgia, Cambria, Verdana, sans-serif
}

#wrapper {
    background: #ffffff;
    margin: 0 auto;
    padding: 3px;
    border: 3px #0000ff solid;
    -webkit-border-radius: 5px;
    border-radius: 5px;
    width: 600px
}

header {
    text-align: center
}

footer {
    text-align: center
}

#guestbook {
    border: 1px #0000ff solid;
    font-size: 13px;
    padding: 5px
   
}
a {
    color: #0000ff;
    text-decoration: none;
}

ul {
    list-style: none
}

label {
    display: block;
    list-style-type: none
}

.message {
    color: #ff0000;
    margin: 0;
    padding: 3px;
    border: 1px #ff0000 solid;
    -webkit-border-radius: 5px;
    border-radius: 5px;
    width: 500px
}

#button {
    background-color: #0080c0;
    border-color: #2528ad;
    border-style: solid;
    color: #ffffff;
    cursor: pointer;
    padding: 5px;
    border-width: 1px;
    font-size: 15px;
    padding: 3px;
    text-align: center;
    text-decoration: none;
    -webkit-border-radius:5px;
    border-radius:5px  
}

- addGuestbook.inc.php

<?php
//addGuestbook.inc.php
include './includes/validateEmailAddress.inc.php';
include './includes/validateURL.inc.php';
$name = (isset($_POST['name'])) ? trim($_POST['name']) : '';
$emailAddress = (isset($_POST['emailAddress'])) ? trim($_POST['emailAddress']) : '';
$website = (isset($_POST['website'])) ? trim($_POST['website']) : '';
$message = (isset($_POST['message'])) ? trim($_POST['message']) : '';
$errors = array();
$success = '';
if(isset($_POST['submit']) && $_POST['submit'] == 'Submit') {

    $dateCreated = date('y-m-d G:i:s');
    if(empty($name)) {
        $errors[] = 'Name required.';
    } //else $isNameOk = 1;
    
    if(empty($emailAddress)) {
        $errors[] = 'Email address required.';
    } else if(!validateEmailAddress($emailAddress)) {
            $errors[] = 'Invalid Email Address.';
        } //else $isEmailAddressOk = 1;
    if(!empty($website)) {
        if(!validateURL($website)) $errors[] = 'Invalid Website address.';
    } //else $isWebsiteOk = 1;
    
    if(empty($message)) {
        $errors[] = 'Message required.';
    } //else $isMessageOk = 1;
    //if($isNameOk == 1 && $isEmailAddressOk && $isWebsiteOk && $isMessageOk) {
        //echo $name.' '.$emailAddress.' '.$website.' '.$message;
    if(count($errors) == 0){
        //echo $name.' '.$emailAddress.' '.$website.' '.$message. ' '.$dateCreated;
        $connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        if(!$connection) echo 'Unable to connect to database server. '.mysql_error();
        $database = mysql_select_db(DB_SCHEMA);
        if(!$database) echo 'Unable to select database. '.mysql_error();
        
        $query = sprintf("INSERT INTO guestbook(id, name, email_address, website, message, date_created)
            VALUES(NULL, '%s', '%s', '%s', '%s', '%s')",mysql_real_escape_string($name), mysql_real_escape_string($emailAddress), mysql_real_escape_string($website), mysql_real_escape_string($message), $dateCreated);
        $result = mysql_query($query);
        if(!$result) echo 'Unable to post your message'.  mysql_error();
        else {
            $success = "Success!";
            $name = '';
            $emailAddress = '';
            $website = '';
            $message = '';
        }
        mysql_close($connection);
    }
}
?>

- dbConfig.inc.php

<?php
//dbConfig.inc.php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'admin@12345');
define('DB_SCHEMA', 'mydb');
?>

- validateEmailAddress.inc.php


<?php
//validateEmailAddress.inc.php
function validateEmailAddress($emailAddress){
    $regexp = "^([_a-z0-9-]+)(\.[_a-z0-9-]+)*@([a-z0-9-]+)(\.[a-z0-9-]+)*(\.[a-z]{2,6})$^i";
    if (preg_match($regexp, $emailAddress)) return true;
    else return false;
}
?>

- validateURL.inc.php

<?php
//validateURL.inc.php
function validateURL($url) {
    $regexp = '|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)?(/.*)?$|i';
    if(preg_match($regexp, $url)) return true;
    else return false;
}
?>

- viewGuestbook.inc.php

<?php
//viewGuestbook.inc.php
$connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$connection) echo "Connection failed. ".mysql_error();  
$database = mysql_select_db(DB_SCHEMA);
if(!$database) echo "Unable to select database. ".mysql_error();

$query = "SELECT * FROM guestbook ORDER BY date_created DESC";
$result = mysql_query($query);
if(!$result) echo "Unable to perform query. ".mysql_error();
if(mysql_num_rows($result) < 1) {
    echo '<div id="guestbook">';
    echo "<p>No message available yet!</p>";
    echo '</div>';
} else {
        while($row = mysql_fetch_array($result)) {  
            $id = $row['id'];
            $name = $row['name'];
            $website = $row['website'];
            if(!empty($website)) $name = '<a href="'.$website.'" target="_blank">'.$name.'</a>';
            else $name = $name;
            $message = $row['message'];
            $date = date_create($row['date_created']);
            $date = date_format($date, 'F d, Y h:i A');
            echo '<p>';
            echo '<div id="guestbook">';
            echo '<p>'.$id.' | '.$name.' | '.$date.'</p>';
            echo '<p>'.$message.'</p>';
            echo '</div>';
            echo '</p>';
    }
}
if(mysql_num_rows($result) > 0) mysql_free_result($result);
mysql_close($connection);
?>

Screenshots

First running of the guestbook; no messages yet

No values entered in the required fields

Only name entered among the required fields

No values entered in the Message field


Invalid Email Address entered


Invalid Website URL




Successful posting of message

Running/viewing the guestbook with messages now available


Conclusion


The guestbook is quite simple but it could be a good script to start with when someone would like to create one. Again, in due time I will develop an enhanced/improved version of this guestbook having advanced features suitable for production Websites.

Download PDF here.

No comments:

Post a Comment