How to Create a PHP/MySQL Powered Forum From Scratch

In this tutorial, we're going to build a PHP/MySQL powered forum from scratch. This tutorial is perfect
for getting used to basic PHP and database usage.

If you need extra help with this or any other PHP issues, try contacting one of the PHP developers on
Envato Studio. They can help you with everything from PHP fixes to developing robust PHP applications.

Step 1: Creating Database Tables

It's always a good idea to start with creating a good data model when building an application. Let's
describe our application in one sentence: We are going to make a forum which has users who create
topics in various categories. Other users can post replies. As you can see, I highlighted a couple of nouns
which represent our table names.

Users

Categories

Topics

Posts

These three objects are related to each other, so we'll process that in our table design. Take a look at
the scheme below.
Looks pretty neat, huh? Every square is a database table. All the columns are listed in it and the lines
between them represent the relationships. I'll explain them further, so it's okay if it doesn't make a lot of
sense to you right now.

I'll discuss each table by explaining the SQL, which I created using the scheme above. For your own
scripts you can create a similar scheme and SQL too. Some editors like MySQL Workbench (the one I
used) can generate .sql files too, but I would recommend learning SQL because it's more fun to do it
yourself. A SQL introduction can be found at W3Schools.

Users Table

CREATE TABLE users (

user_id INT(8) NOT NULL AUTO_INCREMENT,

user_name VARCHAR(30) NOT NULL,

user_pass VARCHAR(255) NOT NULL,

user_email VARCHAR(255) NOT NULL,

user_date DATETIME NOT NULL,

user_level INT(8) NOT NULL,

UNIQUE INDEX user_name_unique (user_name),

PRIMARY KEY (user_id)

) TYPE=INNODB;

The CREATE TABLE statement is used to indicate we want to create a new table, of course. The
statement is followed by the name of the table and all the columns are listed between the brackets. The
names of all the fields are self-explanatory, so we'll only discuss the data types below.

user_id
"A primary key is used to uniquely identify each row in a table."

The type of this field is INT, which means this field holds an integer. The field cannot be empty (NOT
NULL) and increments which each record inserted. At the bottom of the table you can see the user_id
field is declared as a primary key. A primary key is used to uniquely identify each row in a table. No two
distinct rows in a table can have the same value (or combination of values) in all columns. That might be
a bit unclear, so here's a little example.

There is a user called John Doe. If another users registers with the same name, there's a problem,
because: which user is which? You can't tell and the database can't tell either. By using a primary key
this problem is solved, because both topics are unique.

All the other tables have got primary keys too and they work the same way.

user_name

This is a text field, called a VARCHAR field in MySQL. The number between brackets is the maximum
length. A user can choose a username up to 30 characters long. This field cannot be NULL. At the bottom
of the table you can see this field is declared UNIQUE, which means the same username cannot be
registered twice. The UNIQUE INDEX part tells the database we want to add a unique key. Then we
define the name of the unique key, user_name_unique in this case. Between brackets is the field the
unique key applies to, which is user_name.

user_pass

This field is equal to the user_name field, except the maximum length. Since the user password, no
matter what length, is hashed with sha1(), the password will always be 40 characters long.

user_email

This field is equal to the user_pass field.

user_date

This is a field in which we'll store the date the user registered. It's type is DATETIME and the field cannot
be NULL.
user_level

This field contains the level of the user, for example: '0' for a regular user and '1' for an admin. More
about this later.

Categories Tablle

CREATE TABLE categories (

cat_id INT(8) NOT NULL AUTO_INCREMENT,

cat_name VARCHAR(255) NOT NULL,

cat_description VARCHAR(255) NOT NULL,

UNIQUE INDEX cat_name_unique (cat_name),

PRIMARY KEY (cat_id)

) TYPE=INNODB;

These data types basically work the same way as the ones in the users table. This table also has a
primary key and the name of the category must be an unique one.

Topics Table

CREATE TABLE topics (

topic_id INT(8) NOT NULL AUTO_INCREMENT,

topic_subject VARCHAR(255) NOT NULL,

topic_date DATETIME NOT NULL,

topic_cat INT(8) NOT NULL,

topic_by INT(8) NOT NULL,

PRIMARY KEY (topic_id)

) TYPE=INNODB;
Posts Table

CREATE TABLE posts (

post_id INT(8) NOT NULL AUTO_INCREMENT,

post_content TEXT NOT NULL,

post_date DATETIME NOT NULL,

post_topic INT(8) NOT NULL,

post_by INT(8) NOT NULL,

PRIMARY KEY (post_id)

) TYPE=INNODB;

This is the same as the rest of the tables; there's also a field which refers to a user_id here: the post_by
field. The post_topic field refers to the topic the post belongs to.

"A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set
of columns in one (referencing) table that refers to a column or set of columns in another (referenced)
table."

Now that we've executed these queries, we have a pretty decent data model, but the relations are still
missing. Let's start with the definition of a relationship. We're going to use something called a foreign
key. A foreign key is a referential constraint between two tables. The foreign key identifies a column or a
set of columns in one (referencing) table that refers to a column or set of columns in another
(referenced) table. Some conditions:

The column in the referencing table the foreign key refers to must be a primary key

The values that are referred to must exist in the referenced table

By adding foreign keys the information is linked together which is very important for database
normalization. Now you know what a foreign key is and why we're using them. It's time to add them to
the tables we've already made by using the ALTER statement, which can be used to change an already
existing table.
We'll

link the topics to the categories first:

ALTER TABLE topics ADD FOREIGN KEY(topic_cat) REFERENCES categories(cat_id) ON DELETE CASCADE
ON UPDATE CASCADE;

The last part of the query already says what happens. When a category gets deleted from the database,
all the topics will be deleted too. If the cat_id of a category changes, every topic will be updated too.
That's what the ON UPDATE CASCADE part is for. Of course, you can reverse this to protect your data, so
that you can't delete a category as long as it still has topics linked to it. If you would want to do that, you
could replace the 'ON DELETE CASCADE' part with 'ON DELETE RESTRICT'. There is also SET NULL and NO
ACTION, which speak for themselves.

Every topic is linked to a category now.

Let's link the topics to the user who creates one.

ALTER TABLE topics ADD FOREIGN KEY(topic_by) REFERENCES users(user_id) ON DELETE RESTRICT ON
UPDATE CASCADE;

This foreign key is the same as the previous one, but there is one difference: the user can't be deleted as
long as there are still topics with the user id of the user. We don't use CASCADE here because there
might be valuable information in our topics. We wouldn't want that information to get deleted if
someone decides to delete their account. To still give users the opportunity to delete their account, you
could build some feature that anonymizes all their topics and then delete their account. Unfortunately,
that is beyond the scope of this tutorial.

Link the posts to the topics:

ALTER TABLE posts ADD FOREIGN KEY(post_topic) REFERENCES topics(topic_id) ON DELETE CASCADE
ON UPDATE CASCADE;

And finally,

Link each post to the user who made it:

ALTER TABLE posts ADD FOREIGN KEY(post_by) REFERENCES users(user_id) ON DELETE RESTRICT ON
UPDATE CASCADE;
That's the database part! It was quite a lot of work, but the result, a great data model, is definitely worth
it

Step 2: Introduction to the Header/Footer System

Each page of our forum needs a few basic things, like a doctype and some markup. That's why we'll
include a header.php file at the top of each page, and a footer.php at the bottom. The header.php
contains a doctype, a link to the stylesheet and some important information about the forum, such as
the title tag and metatags.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="nl" lang="nl">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<meta name="description" content="A short description." />

<meta name="keywords" content="put, keywords, here" />

<title>PHP-MySQL forum</title>

<link rel="stylesheet" href="style.css" type="text/css">

</head>

<body>

<h1>My forum</h1>

<div id="wrapper">

<div id="menu">

<a class="item" href="/forum/index.php">Home</a> -

<a class="item" href="/forum/create_topic.php">Create a topic</a> -

<a class="item" href="/forum/create_cat.php">Create a category</a>

<div id="userbar">Hello Example. Not you? Log out.</div>

</div>

<div id="content">

The wrapper div will be used to make it easier to style the entire page. The menu div obviously contains
a menu with links to pages we still have to create, but it helps to see where we're going a little bit. The
userbar div is going to be used for a small top bar which contains some information like the username
and a link to the logout page. The content page holds the actual content of the page, obviously.

The attentive reader might have already noticed we're missing some things. There is no </body> or
</html> tag. They're in the footer.php page, as you can see below.

</div><!-- content -->

</div><!-- wrapper -->

<div id="footer">Created for Nettuts+</div>

</body>

</html>

When we include a header and a footer on each page the rest of the page get embedded between the
header and the footer. This method has got some advantages. First and foremost, everything will be
styled correctly. A short example:

<?php

$error = false;

if($error = false)

//the beautifully styled content, everything looks good

else

//bad looking, unstyled error :-(

?>

As you can see, a page without errors will result in a nice page with the content. But if there's an error,
everything looks really ugly; so that's why it's better to make sure not only real content is styled
correctly, but also the errors we might get.

Another advantage is the possibility of making quick changes. You can see for yourself by editing the text
in footer.php when you've finished this tutorial; you'll notice that the footer changes on every page
immediately. Finally, we add a stylesheet which provides us with some basic markup - nothing too fancy.

body {

background-color: #4E4E4E;

text-align: center; /* make sure IE centers the page too */

#wrapper {

width: 900px;

margin: 0 auto; /* center the page */

#content {
 background-color: #fff;

border: 1px solid #000;

float: left;

font-family: Arial;

padding: 20px 30px;

text-align: left;

width: 100%; /* fill up the entire div */

#menu {

float: left;

border: 1px solid #000;

border-bottom: none; /* avoid a double border */

clear: both; /* clear:both makes sure the content div doesn't float next to this one but stays
under it */

width:100%;

height:20px;

padding: 0 30px;

background-color: #FFF;

text-align: left;

font-size: 85%;

#menu a:hover {

background-color: #009FC1;

}
#userbar {

background-color: #fff;

float: right;

width: 250px;

#footer {

clear: both;

/* begin table styles */

table {

border-collapse: collapse;

width: 100%;

table a {

color: #000;

table a:hover {

color:#373737;

text-decoration: none;

}
th {

background-color: #B40E1F;

color: #F0F0F0;

td {

padding: 5px;

/* Begin font styles */

h1, #footer {

font-family: Arial;

color: #F1F3F1;

h3 {margin: 0; padding: 0;}

/* Menu styles */

.item {

background-color: #00728B;

border: 1px solid #032472;

color: #FFF;

font-family: Arial;

padding: 3px;
 text-decoration: none;

.leftpart {

width: 70%;

.rightpart {

width: 30%;

.small {

font-size: 75%;

color: #373737;

#footer {

font-size: 65%;

padding: 3px 0 0 0;

.topic-post {

height: 100px;

overflow: auto;

}
.post-content {

padding: 30px;

textarea {

width: 500px;

height: 200px;

Step 3: Getting Ready for Action

Before we can read anything from our database, we need a connection. That's what connect.php is for.
We'll include it in every file we are going to create.

<?php

//connect.php

$server = 'localhost';

$username = 'usernamehere';

$password = 'passwordhere';

$database = 'databasenamehere';

if(!mysql_connect($server, $username, $password))

exit('Error: could not establish database connection');

}
if(!mysql_select_db($database)

exit('Error: could not select the database');

?>

Step 4: Displaying the Forum Overview

Since we're just started with some basic techniques, we're going to make a simplified version of the
forum overview for now.