PHP 101 Project: Building a Guestbook

Hi! If you want to get into working with database back-ends, then hopefully this guide will help!

I’m going to walk through building a guestbook that has three of the four vital functions of a modern web app: creating, reading, and deleting.

This guide is designed for those with no PHP knowledge, and for those with leprd.space installs, but can be used by anyone with Apache on cPanel!

I remember reading a ton of tutorials when I set out to build my first PHP app. I felt like a lot of them didn’t really walk you through each step, so I’m going to try and write this how I would have liked to find a guide when I was starting out.

I recommend reading my Introduction to Databases article if you’re totally new. As always I recommend reading through the entire guide before attempting, or at least skimming each section because some helpful tips come after each instruction!

Finally, if you run into issues along the way, feel free to compare your code to your my Github repo of this project. If I made any mistakes in this tutorial, please let me know!

Create and assign permissions to the database

Before we begin, we need to create a database and assign a user permissions to access the database. I have a guide for how to do exactly this here.

You’ll want to create a database, you can call it something like username_guestbook (your username is always prefixed before the db name). Then, use the guide linked above to assign a user permissions to that database.

Create the table

Go to phpMyAdmin and select your database from the left sidebar. You’ll see it says Create Table. For this tutorial, we’re going to choose the name guestbook. For number of columns, change the number to six. Then click the Go button.

The next page will ask you to fill out information about each column. You can follow this screenshot for the values of each column:

For the id column, you only have to tick the AI box and click through the corresponding prompt. This means the id column will always increment by 1 with each entry, assigning a unique numeric value which will come in handy!

Another thing to note is the difference between VARCHAR and LONGTEXT. The VARCHAR field can only hold up to 255 characters, while the LONGTEXT type can hold much longer data.

When it’s all filled out, click on Save which will take you to phpMyAdmin’s view of your database, which is currently empty.

Create your config file

Head on over to your File Manager and go into your public_html folder. Create a new folder and call it guestbook.

Go inside of your new folder and create a new file called config.php. This file will hold the “connection” to your database. Paste in the following text:

<?php $host = "localhost"; /* this will always be localhost */ $user = "username_guestbook"; /* User */ $password = "password"; /* Password */ $dbname = "username_guestbook"; /* Database name */ $con = mysqli_connect($host, $user, $password, $dbname); // this checks the connection if (!$con) { die("Connection failed: " . mysqli_connect_error()); }
Code language: PHP (php)

Now, go through it and change the $user, $password, and $dbname variables to match your own. Save the page.

To test your database connection, open a browser and go to https://username.leprd.space/guestbook/config.php, after replacing ‘username’ with your username of course.

If the connection was successful, you’ll see a blank page. If it was not successful, you’ll see an error.

Creating our guestbook form

To begin, go into the same directory as config.php and create a file called index.php. Even though this is a PHP file (and project) we’re gonna start by writing some HTML.

The most common way HTML interacts with PHP is through a form.

<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Guestbook</title> </head> <body> <div class="container"> <form method="POST" action="submit.php"> <label>Name:</label> <input type="text" name="name" required><br> <label>Email:</label> <input type="text" name="email"><br> <label>Website:</label> <input type="url" name="website"><br> <label>Message:</label> <textarea name="message" name="message" required></textarea><br> <label>Please enter the word 'website': <input type="hidden" name="botField"> <input type="submit" name="submit" value="Submit"> </form> </div> </body> </html>
Code language: HTML, XML (xml)

The above form lists four fields: name, email, website and message. Only the email and website fields are optional.

We also added a botField, which will essentially work as a basic “captcha” to prevent bots from spamming your guestbook with entries. On the PHP end, we’ll check this input box for a value, and if it doesn’t match our chosen word (in this case website), your code will automatically reject the submission.

Save your file. The form’s action tells the form where to send the data upon submit. We reference a file called submit.php but we haven’t created it yet so let’s get to it!

Creating the submission logic

Return to your file manager and create another file in your directory. Call this one submit.php

To start with, we’re going to want to include the config.php file, since that file has the credentials needed to access the database. We need to do this on every page that requires access to the database.

<?php include: 'config.php'; ?>
Code language: PHP (php)

Then, we want to use an isset statement so the database query only loads when a specific condition is met. The condition we want to use is “when the submit button is pressed…”.

When using the form’s action field to send the logic, PHP can identify form values by the name field. This is why our form HTML included names for each field.

<?php include: 'config.php'; if (isset($_POST['submit'])) { // write your code here } ?>
Code language: PHP (php)

The isset() function just checks if a field with the name value of submit was submitted. Once that condition is met, the code runs.

Check if the ‘captcha’ field was filled

The first thing we want to do once the form is submitted is to check if the captcha field was filled out correctly.

<?php include: 'config.php'; if (isset($_POST['submit'])) { if ($botField !== "website") { return; } else { // write your code here } } ?>
Code language: HTML, XML (xml)

Define our form submission variables

Once we’ve made the logic for the bot field, we want to define the variables we’re working with. Those variables, in this case, are the form fields that have been entered over on our form.

<?php include: 'config.php'; if (isset($_POST['submit'])) {\ $botField = $_POST['botField']; if ($botField !== "website") { return; } else { // this defines our variables $name = $_POST['name']; $email = $_POST['email']; $website = $_POST['website']; $message = $_POST['message']; } } ?>
Code language: PHP (php)

Grab the current datetime

Since we want to know what time a comment was submitted, the first thing we need to do is set the time zone in PHP.

For this example, I’m using EST but you can visit this page to see a list of the correct wording you need for each timezone.

<?php include: 'config.php'; if (isset($_POST['submit'])) { $botField = $_POST['botField']; if ($botField !== "website") { return; } else { $name = $_POST['name']; $email = $_POST['email']; $website = $_POST['website']; $message = $_POST['message']; // this sets the time zone date_default_timezone_set("US/Eastern"); } } ?>
Code language: PHP (php)

Then, we need to capture the datetime. For this we can use date(). In the following example, I’m outputting the full datetime, like: 2020-09-20 05:50:00, which is how the SQL database seems to work best with the data.

<?php include: 'config.php'; if (isset($_POST['submit'])) { $botField = $_POST['botField']; if ($botField !== "website") { return; } else { $name = $_POST['name']; $email = $_POST['email']; $website = $_POST['website']; $message = $_POST['message']; date_default_timezone_set("US/Eastern"); // this formats the date for the database $datetime = date("Y-m-d H:i:s"); } } ?>
Code language: PHP (php)

Create a connection to the database

There are two main ways to connect PHP to a database. One is via MySQL and one is via PDO. For this tutorial, we are following the MySQL connection method, but you are encouraged to do your research and see which is right for you!

You can check my Github notes project to find templates for each kind of query when using PHP/MySQL to connect to a database.

Since we are inserting records into the database with each form submission, we want an insert statement.

View the example below, paying attention to comments around the database query.

<?php include 'config.php'; if (isset($_POST['submit'])) { $botField = $_POST['botField']; if ($botField !== "website") { return; } else { $name = $_POST['name']; $email = $_POST['email']; $website = $_POST['website']; $message = $_POST['message']; date_default_timezone_set("US/Eastern"); $datetime = date("Y-m-d H:i:s"); // this prepares our query (it gets the $con variable from config.php) $stmt = $con->prepare("INSERT INTO guestbook(datetime, name, email, website, message) VALUES (?,?,?,?,?)"); // this binds our variables to the ones in the statement $stmt->bind_param("sssss", $datetime, $name, $email, $website, $message); // this executes the query $stmt->execute(); // this closes the connection $stmt->close(); } } ?>
Code language: PHP (php)

One thing left to do: when the query is finished executing, we want the user to be redirected back to index.php, not the blank submit.php page. We can do this by using a location redirect:

<?php include 'config.php'; if (isset($_POST['submit'])) { $botField = $_POST['botField']; if ($botField !== "website") { return; } else { $name = $_POST['name']; $email = $_POST['email']; $website = $_POST['website']; $message = $_POST['message']; date_default_timezone_set("US/Eastern"); $datetime = date("Y-m-d H:i:s"); // this prepares our query (it gets the $con variable from config.php) $stmt = $con->prepare("INSERT INTO guestbook(datetime, name, email, website, message) VALUES (?,?,?,?,?)"); // this binds our variables to the ones in the statement $stmt->bind_param("sssss", $datetime, $name, $email, $website, $message); // this executes the query $stmt->execute(); // this closes the connection $stmt->close(); // this redirects the user header('location: index.php'); } } ?>
Code language: PHP (php)

No code after a header redirect will be run, so when using this to redirect users, make sure it’s always the last line that runs.

Test your guestbook

To test your form, go to https://username.leprd.space/guestbook/ and fill out the form, then click submit. To verify your submission was successful, return to your cPanel Dashboard and go into phpMyAdmin and view the guestbook database. You should see your new entry! If you run into errors along the way – remember: check the error_log file for information about your particular error.

Display the guestbook entries

We’re going to display the guestbook entries beneath our form, which means we want to go and edit index.php with this information.

We’ll need to execute another database query to retrieve this data. Since we just want to read what’s there, we need a SELECT statement. Paste this under your </form> tag, but before the </div> for the container.

You’ll see this looks somewhat familiar to our INSERT statement, but there’s something new – a while statement. This is a loop that goes through each item in the database.

<?php include "config.php"; // include our config $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do stuff } $stmt->close(); ?>
Code language: PHP (php)

Now we need to add stuff to our while loop. Inside of the while loop, we access column values by name, like $row[“columnName”]

<?php include "config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; } $stmt->close(); ?>
Code language: PHP (php)

Next, we need to display these values somehow. You can display a PHP variable by echoing it. For example, to display the name, we could use: echo $name;

It would look pretty messy if we just displayed the values by themselves though. You’ll want to use HTML with your PHP and you have two general options:

  • You can echo each line of HTML while concatenating (combining) PHP variables with strings (e.g., echo "<div>" . $name . "</div>;)
  • You can “end” the PHP mid-loop by ending the PHP tag and then using HTML as usual while including PHP variables in their usual tags (e.g., <div><?php echo $name; ?></div>)

Personally, I find the second way a lot easier to keep track of things, so that’s what we’re gonna use.

Here is an example of how I’m breaking up the PHP code we’ve constructed:

<?php include "config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; ?> <!-- now I can write my HTML ~inside~ of the "while" loop --> <?php } // this bracket ends the while loop $stmt->close(); ?>
Code language: PHP (php)

Building off of the example above, here is how I might format the data from the database:

<?php include "config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; ?> <!-- each individual comment field is wrapped in a div --> <!-- and each comment item is also wrapped inside of their own div --> <!-- I separated the label from the "Message" div so it would go on a newline --> <div class="comment"> <div><strong>Date: </strong><?php echo $datetime; ?></div> <div><strong>From: </strong><?php echo $name; ?></div> <div><strong>Website:</strong><?php echo $website; ?></div> <div><strong>Message </strong></div> <div><?php echo $message; ?></div> </div> <?php } $stmt->close(); ?>
Code language: PHP (php)

Format the date

The date is looking kind of messy, but we can format it any way we want to. Here is a link to the official PHP documentation for text formatting. Since we’re creating a new variable for the date, we also need to change the variable we’re echoing.

<?php include "config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; // format date $formattedDate = date("l, F j Y", strtotime($datetime)); // outputs "such as" Wednesday, March 9 2022 // format time $formattedTime = date("g:iA", strtotime($datetime)); // outputs "such as" 8:36PM ?> <div class="comment"> <div><strong>Date: </strong><?php echo $formattedDate . " at " . $formattedTime; ?></div> <div><strong>From: </strong><?php echo $name; ?></div> <div><strong>Website: </strong><?php echo $website; ?></div> <div><strong>Message </strong></div> <div><?php echo $message; ?></div> </div> </div> <?php } $stmt->close(); ?>
Code language: PHP (php)

Show the website field conditionally

Since the ‘website’ field is optional, we don’t want to show it if someone doesn’t submit a value for that field. We can use PHP’s empty() function to check if a value is empty before echoing the value. In this example we added the not operator ! to show we are checking for only non-empty values before displaying.

We also moved the label inside of the PHP because we don’t want an empty “Website:” label floating there.

<?php include "config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; // format date $formattedDate = date("l, F j Y", strtotime($datetime)); // outputs "such as" Wednesday, March 9 2022 // format time $formattedTime = date("g:iA", strtotime($datetime)); // outputs 8:36PM ?> <div class="comment"> <div><strong>Date: </strong><?php echo $formattedDate . " at " . $formattedTime; ?></div> <div><strong>From: </strong><?php echo $name; ?></div> <div> <?php if (!empty($website)){ echo "<strong>Website: </strong>" . $website; }?> </div> <div><strong>Message:</strong></div> <div><?php echo $message; ?></div> </div> <?php } $stmt->close(); ?>
Code language: PHP (php)

Create the Admin View

We’re almost done! We’re sooo close to the end! All we need to do is make a mini-admin area where you can log in and delete any comments that need to be deleted (you know the ones).

To start, let’s make a new folder, and call it admin.

In this folder, create two files: index.php and edit.php

Go to your index.php file from your main directory and copy its contents. Paste it into your new admin/index.php folder, and delete the <form></form> tags and everything between them.

Edit the line include 'config.php' to be include '../config.php'; – this is because our config.php file is now one directory up from where we’re calling it.

Then, at the bottom of our .comment div, let’s add a delete button. Since we want to POST the variables to PHP, we’ll need to wrap the button in a form.

I also added a hidden field and assigned the value to the comment’s ID. This is because we’ll need the exact ID of the item being clicked on in order to delete it.

All together, our PHP code would look like this:

<?php include "../config.php"; $stmt = $con->prepare("SELECT * FROM guestbook"); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $id = $row["id"]; $datetime = $row["datetime"]; $name = $row["name"]; $email = $row["email"]; $website = $row["website"]; $message = $row["message"]; $formattedDate = date("l, F j Y", strtotime($datetime)); $formattedTime = date("g:iA", strtotime($datetime)); ?> <div class="comment"> <div><strong>Date:</strong><?php echo $formattedDate . " at " . $formattedTime; ?></div> <div><strong>From:</strong><?php echo $name; ?></div> <div> <?php if (!empty($website)){ echo "<strong>Website: </strong>" . $website; }?> </div> <div><strong>Message </strong></div> <div><?php echo $message; ?></div> <!-- create a form with to POST data to index.php (the same file you're writing this in) --> <form method="POST" action="index.php"> <!-- this is a hidden field for capturing and passing the id to the query --> <input type="hidden" name="submitID" value="<?php echo $id; ?>"> <!-- we are using the name submitDel to reference the submit button click --> <input type="submit" name="submitDel" value="Delete"> </form> </div> <?php } $stmt->close(); ?>
Code language: PHP (php)

Create deletion logic

We’re gonna use the same file for the deletion logic, because why not.

Let’s start, again, by using an isset() statement to check if the submitEdit variable has been POSTed.

Add this PHP after the final } bracket on admin/index.php, but before the ?> closing PHP tag:

if (isset($_POST['submitDel'])) { $id = $_POST['submitID']; $stmt = $con->prepare("DELETE FROM guestbook WHERE id = ?"); $stmt->bind_param("s", $id); $stmt->execute(); $stmt->close(); // this redirects the user back to /admin/index.php header('location: index.php'); }
Code language: PHP (php)

DELETE queries are always the easiest. In the example above, the query is referencing the unique ID of the comment in order to delete the correct item.

Save the file.

Test the admin view

You should be able to view things at https://username.leprd.space/guestbook/admin/

You will see a list of entries and a delete button at the bottom of each one. Try clicking the delete button to see what happens. If you encounter any errors, remember to check the error_log file to see what went wrong.

Password-protect the admin view

You can find an article I wrote about using htaccess to password protect certain directories here. I recommend using the “to protect a directory” instructions and placing both .htaccess and .htpasswd files inside of your admin folder to ensure no one can access this page but you.

Style your page, add fields, have fun!

At this point, you have a fully functioning guestbook with a database back-end! You can create, view and delete comments. We didn’t delve into editing in this tutorial but there will be plenty of time for that in the future.

At this point, you should think about adding some CSS styling to your guestbook. Play around with how using HTML inside the while loop works – keep in mind that everything inside of that loop will display once for every database item (which in this case is guestbook comments!)

If you run into any problems along the way, you can also compare your code to my Github repo for this project.



Hey, thanks for reading! If you have any questions, you can leave me a comment and I always try to reply.

If you have any suggestions on how I can improve this article, please let me know!

This website is not monetized in any way. It purely exists to help others on their web-building journey. However, if you have the means to donate toward my server costs, I have a ko-fi account where I accept tips.

1 thought on “PHP 101 Project: Building a Guestbook”

Leave a Comment