• Tweet

In this post we’re going to see how we can build a guestbook easily with PHP and MySQL. This is very simple and straightforward. All we need is:

  • A webserver or a hosting account
  • phpMyAdmin or something similar for database access

guestbook 300x253 Building your own guestbook with PHP and MySQL

First, we have to create a database and the necessary tables. Go to your control panel (cPanel or something) and go to databases. Create a new database. Notice the database name. Now create an user and add the user to that database. If you find this difficult, please contact the customer service of your hosting provider. The process significantly differs from host to host depending on the control panel they provide. Please note the full database name, user name and database host. In most cases the database host is “localhost”. But some providers ask customers to use a remote mysql host. In that case, please note that host down. If you’re using WAMP server on Windows, go to http://localhost/phpmyadmin/ and you shall get the options there.

Now we’re ready to import the table. Go to phpMyAdmin, select the database you created and use the following SQL commands to create the table:

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `timestamp` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 

Okay, so if you execute the command, we shall have a table named “comments” with 6 fields. The “id” field is the primary key and the unique identifier. The other fields are – name, email, website, message and timestamp. The timestamp field stores the unix epoch value (how many seconds have passed after 1st January 1970) of time. It will be generated using the time() function of php. The other fields are self explanatory.

Now we shall create a configuration file which shall be used to store the mysql connection data. Create a file named: “config.php” and put the following codes in it:

<?php
$host = "localhost";
$username = "username";
$password = "password";
$database = "guestbook";
mysql_connect($host,$username,$password);
mysql_select_db($database);
?>

So, the config.php lets you use your own configuration data. Feel free to change the values of the variables to match your setup.

Now we create the first page. Create a file named “index.php” and put these codes into it:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
        <title>My Little Guest Book</title>
</head>
<body>
<h3>Post A Comment:</h3>
<form action="post.php" method="post">
   <strong>Name:</strong><br/> <input type="text" name="name" /><br/>
    <strong>Email:</strong><br/> <input type="text" name="email" /><br/>
    <strong>Website:</strong><br/> <input type="text" name="website" /><br/>
    <strong>Message:</strong><br/> <textarea name="message" rows="5" cols="25"></textarea><br/>
    <input type="submit" value="Go">
</form>
<h3>Exisiting Comments:</h3>
<?php
require_once 'config.php';
$allPostsQuery = mysql_query("select * from comments order by `timestamp` DESC ");
if(mysql_num_rows($allPostsQuery) < 1) {
    echo "No comments were found!";
} else {
    while($comment = mysql_fetch_assoc($allPostsQuery)) {
        echo "<b>Name:</b> {$comment['name']} <br/>";
        echo "<b>Email:</b> {$comment['email']} <br/>";
        echo "<b>Website:</b> {$comment['website']} <br/>";
        echo "<b>Message:</b> {$comment['message']} <br/>";
        echo "<b>Posted at:</b> " .date("Y-d-m H:i:s",$comment['timestamp']). " <br/><hr/>";
    }
}
?>
</body>
</html>

So what does this script do? It has a html form to fill up the data necessary. After the form, we fetch all the comments stored in the database and display them on the page. If no comments are found, we also print that out. Did you notice that the form action is “post.php”? Yes, the form data will be sent to posts.php which shall process the fields and store them in database. So, let’s crate post.php and put the following codes:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
     <title>My Little Guest Book</title>
</head>
<body>
<?php
require_once 'config.php';
$fields = array("name", "website", "email", "message");
$isOkay = TRUE;
foreach ($fields as $field) {
    if (empty($_POST[$field])) {
        $isOkay = FALSE;
    }
}
if ($isOkay) {
    extract($_POST);
    $now = time();
    if (mysql_query("insert into comments (`name`,website,email,message,`timestamp` ) values ('{$name}','$website','$email','$message','{$now}')")) {
        header("Location: index.php");
    } else {
        echo "There was an error connecting to the database!";
    }
} else {
    echo "One or more fields are empty!";
}
?>
</body>
</html>

On this page, we check if all the fields were filled out. If not, we ask print an error message. If all the fields were filled out, we try to store the contents to database. We create the timestamp value using time() and extract the keys out of $_POST variable. If the database insertion fails, we also print out an error message. If everything is okay, we forward the visitor to the index.php where he or she shall be able to see the comments just below the form.

We’re now done with our little guestbook. It is not that feature rich but it works! In the coming posts, we shall see how we can modify it to add validation, pagination and ajax interactions. Stay tuned!