# 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.

## 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

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 Table

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

This table is almost the same as the other tables, except for the topic_by field. That field refers to the user who created the topic. The topic_cat refers to the category the topic belongs to. We cannot force these relationships by just declaring the field. We have to let the database know this field must contain an existing user_id from the users table, or a valid cat_id from the categories table. We'll add some relationships after I've discussed the posts table.

### Posts Table

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:

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.

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:

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.

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.

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:

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.

## 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.

Simply replace the default values of the variables at the top of the page with your own date, save the file and you're good to go!

## 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.

There you have it: a nice and clean overview. We'll be updating this page throughout the tutorial so that it becomes more like the end result, step by step!

## Step 5: Signing up a User

Let's start by making a simple HTML form so that a new user can register.

## Step 8: Adding Categories to index.php

We've created some categories, so now we're able to display them on the front page. Let's add the following query to the content area of index.php.

This query selects all categories and their names and descriptions from the categories table. We only need a bit of PHP to display the results. If we add that part just like we did in the previous steps, the code will look like this.

Notice how we're using the cat_id to create links to category.php. All the links to this page will look like this: category.php?cat_id=x, where x can be any numeric value. This may be new to you. We can check the url with PHP for $_GET values. For example, we have this link: The statement echo$_GET[ëcat_id'];' will display '23'. In the next few steps we'll use this value to retrieve the topics when viewing a single category, but topics can't be viewed if we haven't created them yet. So let's create some topics!

## Step 9: Creating a Topic

In this step, we're combining the techniques we learned in the previous steps. We're checking if a user is signed in, we'll use an input query to create the topic and create some basic HTML forms.

The structure of create_topic.php can hardly be explained in a list or something, so I rewrote it in pseudo-code.

Here's the real code of this part of our forum, check the explanations below the code to see what it's doing.

I'll discuss this page in two parts, showing the form and processing the form.

Showing the formWe're starting with a simple HTML form. There is actually something special here, because we use a dropdown. This dropdown is filled with data from the database, using this query:

That's the only potentially confusing part here; it's quite a piece of code, as you can see when looking at the create_topic.php file at the bottom of this step.

Processing the form

The process of saving the topic consists of two parts: saving the topic in the topics table and saving the first post in the posts table. This requires something quite advanced that goes a bit beyond the scope of this tutorial. It's called a transaction, which basically means that we start by executing the start command and then rollback when there are database errors and commit when everything went well. More about transactions.

The first query being used to save the data is the topic creation query, which looks like this:

At first the fields are defined, then the values to be inserted. We've seen the first one before, it's just a string which is made safe by using mysql_real_escape_string(). The second value, NOW(), is a SQL function for the current time. The third value, however, is a value we haven't seen before. It refers to a (valid) id of a category. The last value refers to an (existing) user_id which is, in this case, the value of \$_SESSION[ëuser_id']. This variable was declared during the sign in process.

If the query executed without errors we proceed to the second query. Remember we are still doing a transaction here. If we would've got errors we would have used the ROLLBACK command.

The first thing we do in this code is use mysql_insert_id() to retrieve the latest generated id from the topic_id field in the topics table. As you may remember from the first steps of this tutorial, the id is generated in the database using auto_increment.

Then the post is inserted into the posts table. This query looks a lot like the topics query. The only difference is that this post refers to the topic and the topic referred to a category. From the start, we decided to create a good data model and here is the result: a nice hierarchical structure.

## Step 10: Category View

We're going to make an overview page for a single category. We've just created a category, it would be handy to be able to view all the topics in it. First, create a page called category.php.

A short list of the things we need:

### Needed for displaying the category

• cat_name
• cat_description

Needed for displaying all the topics

• topic_id
• topic_subject
• topic_date
• topic_cat

Let's create the two SQL queries that retrieve exactly this data from the database.

The query above selects all the categories from the database.

The query above is executed in the while loop in which we echo the categories. By doing it this way, we'll see all the categories and the latest topic for each of them.The complete code of category.php will be the following:

And here is the final result of our categories page:

## Step 11: Topic View

The SQL queries in this step are complicated ones. The PHP-part is all stuff that you've seen before. Let's take a look at the queries. The first one retrieves basic information about the topic:

This information is displayed in the head of the table we will use to display all the data. Next, we retrieve all the posts in this topic from the database. The following query gives us exactly what we need:

This time, we want information from the users and the posts table - so we use the LEFT JOIN again. The condition is: the user id should be the same as the post_by field. This way we can show the username of the user who replied at each post.

The final topic view looks like this:

Let's create the last missing part of this forum, the possibility to add a reply. We'll start by creating a form:

The complete reply.php code looks like this.

The comments in the code pretty much detail what's happening. We're checking for a real user and then inserting the post into the database.

## Finishing Up

Now that you've finished this tutorial, you should have a much better understanding of what it takes to build a forum. I hope my explanations were clear enough! Thanks again for reading.