How to Create a PHP/MySQL Powered Forum from Scratch_part3(end)

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.

1. SELECT
2.     categories.cat_id,
3.     categories.cat_name,
4.     categories.cat_description,
5. FROM
6.     categories

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.

1. <?php
2. //create_cat.php
3. include 'connect.php';
4. include 'header.php';
5.  
6. $sql = "SELECT
7.             cat_id,
8.             cat_name,
9.             cat_description,
10.         FROM
11.             categories";
12.  
13. $result = mysql_query($sql);
14.  
15. if(!$result)
16. {
17.     echo 'The categories could not be displayed, please try again later.';
18. }
19. else
20. {
21.     if(mysql_num_rows($result) == 0)
22.     {
23.         echo 'No categories defined yet.';
24.     }
25.     else
26.     {
27.         //prepare the table
28.         echo '<table border="1">
29.               <tr>
30.                 <th>Category</th>
31.                 <th>Last topic</th>
32.               </tr>'; 
33.              
34.         while($row = mysql_fetch_assoc($result))
35.         {               
36.             echo '<tr>';
37.                 echo '<td class="leftpart">';
38.                     echo '<h3><a href="category.php?id">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'];
39.                 echo '</td>';
40.                 echo '<td class="rightpart">';
41.                             echo '<a href="topic.php?id=">Topic subject</a> at 10-10';
42.                 echo '</td>';
43.             echo '</tr>';
44.         }
45.     }
46. }
47.  
48. include 'footer.php';
49. ?>

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:

1. category.php?cat_id=23

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.

1. <?php
2. if(user is signed in)
3. {
4.     //the user is not signed in
5. }
6. else
7. {
8.     //the user is signed in
9.     if(form has not been posted)
10.     {   
11.         //show form
12.     }
13.     else
14.     {
15.         //process form
16.     }
17. }
18. ?>

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

1. <?php
2. //create_cat.php
3. include 'connect.php';
4. include 'header.php';
5.  
6. echo '<h2>Create a topic</h2>';
7. if($_SESSION['signed_in'] == false)
8. {
9.     //the user is not signed in
10.     echo 'Sorry, you have to be <a href="/forum/signin.php">signed in</a> to create a topic.';
11. }
12. else
13. {
14.     //the user is signed in
15.     if($_SERVER['REQUEST_METHOD'] != 'POST')
16.     {   
17.         //the form hasn't been posted yet, display it
18.         //retrieve the categories from the database for use in the dropdown
19.         $sql = "SELECT
20.                     cat_id,
21.                     cat_name,
22.                     cat_description
23.                 FROM
24.                     categories";
25.          
26.         $result = mysql_query($sql);
27.          
28.         if(!$result)
29.         {
30.             //the query failed, uh-oh :-(
31.             echo 'Error while selecting from database. Please try again later.';
32.         }
33.         else
34.         {
35.             if(mysql_num_rows($result) == 0)
36.             {
37.                 //there are no categories, so a topic can't be posted
38.                 if($_SESSION['user_level'] == 1)
39.                 {
40.                     echo 'You have not created categories yet.';
41.                 }
42.                 else
43.                 {
44.                     echo 'Before you can post a topic, you must wait for an admin to create some categories.';
45.                 }
46.             }
47.             else
48.             {
49.          
50.                 echo '<form method="post" action="">
51.                     Subject: <input type="text" name="topic_subject" />
52.                     Category:'; 
53.                  
54.                 echo '<select name="topic_cat">';
55.                     while($row = mysql_fetch_assoc($result))
56.                     {
57.                         echo '<option value="' . $row['cat_id'] . '">' . $row['cat_name'] . '</option>';
58.                     }
59.                 echo '</select>'; 
60.                      
61.                 echo 'Message: <textarea name="post_content" /></textarea>
62.                     <input type="submit" value="Create topic" />
63.                  </form>';
64.             }
65.         }
66.     }
67.     else
68.     {
69.         //start the transaction
70.         $query  = "BEGIN WORK;";
71.         $result = mysql_query($query);
72.          
73.         if(!$result)
74.         {
75.             //Damn! the query failed, quit
76.             echo 'An error occured while creating your topic. Please try again later.';
77.         }
78.         else
79.         {
80.      
81.             //the form has been posted, so save it
82.             //insert the topic into the topics table first, then we'll save the post into the posts table
83.             $sql = "INSERT INTO 
84.                         topics(topic_subject,
85.                                topic_date,
86.                                topic_cat,
87.                                topic_by)
88.                    VALUES('" . mysql_real_escape_string($_POST['topic_subject']) . "',
89.                                NOW(),
90.                                " . mysql_real_escape_string($_POST['topic_cat']) . ",
91.                                " . $_SESSION['user_id'] . "
92.                                )";
93.                       
94.             $result = mysql_query($sql);
95.             if(!$result)
96.             {
97.                 //something went wrong, display the error
98.                 echo 'An error occured while inserting your data. Please try again later.' . mysql_error();
99.                 $sql = "ROLLBACK;";
100.                 $result = mysql_query($sql);
101.             }
102.             else
103.             {
104.                 //the first query worked, now start the second, posts query
105.                 //retrieve the id of the freshly created topic for usage in the posts query
106.                 $topicid = mysql_insert_id();
107.                  
108.                 $sql = "INSERT INTO
109.                             posts(post_content,
110.                                   post_date,
111.                                   post_topic,
112.                                   post_by)
113.                         VALUES
114.                             ('" . mysql_real_escape_string($_POST['post_content']) . "',
115.                                   NOW(),
116.                                   " . $topicid . ",
117.                                   " . $_SESSION['user_id'] . "
118.                             )";
119.                 $result = mysql_query($sql);
120.                  
121.                 if(!$result)
122.                 {
123.                     //something went wrong, display the error
124.                     echo 'An error occured while inserting your post. Please try again later.' . mysql_error();
125.                     $sql = "ROLLBACK;";
126.                     $result = mysql_query($sql);
127.                 }
128.                 else
129.                 {
130.                     $sql = "COMMIT;";
131.                     $result = mysql_query($sql);
132.                      
133.                     //after a lot of work, the query succeeded!
134.                     echo 'You have successfully created <a href="topic.php?id='. $topicid . '">your new topic</a>.';
135.                 }
136.             }
137.         }
138.     }
139. }
140.  
141. include 'footer.php';
142. ?>

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

Showing the form

We'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:

1. SELECT
2.     cat_id,
3.     cat_name,
4.     cat_description
5. FROM
6.     categories

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.

1. <?php
2. //start the transaction
3. $query  = "BEGIN WORK;";
4. $result = mysql_query($query);
5. //stop the transaction
6. $sql = "ROLLBACK;";
7. $result = mysql_query($sql);
8. //commit the transaction
9. $sql = "COMMIT;";
10. $result = mysql_query($sql);
11. ?>

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

1. INSERT INTO
2.     topics(topic_subject,
3.                topic_date,
4.                topic_cat,
5.                topic_by)
6. VALUES('" . mysql_real_escape_string($_POST['topic_subject']) . "',
7.        NOW(),
8.        " . mysql_real_escape_string($_POST['topic_cat']) . ",
9.        " . $_SESSION['user_id'] . ")

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.

1. INSERT INTO
2.         posts(post_content,
3.         post_date,
4.         post_topic,
5.         post_by)
6. VALUES
7.         ('" . mysql_real_escape_string($_POST['post_content']) . "',
8.          NOW(),
9.          " . $topicid . ",
10.          " . $_SESSION['user_id'] . ")

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.

1. SELECT
2.     cat_id,
3.     cat_name,
4.     cat_description
5. FROM
6.     categories
7. WHERE
8.     cat_id = " . mysql_real_escape_string($_GET['id'])

The query above selects all the categories from the database.

1. SELECT 
2.     topic_id,
3.     topic_subject,
4.     topic_date,
5.     topic_cat
6. FROM
7.     topics
8. WHERE
9.     topic_cat = " . mysql_real_escape_string($_GET['id'])

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:

1. <?php
2. //create_cat.php
3. include 'connect.php';
4. include 'header.php';
5.  
6. //first select the category based on $_GET['cat_id']
7. $sql = "SELECT
8.             cat_id,
9.             cat_name,
10.             cat_description
11.         FROM
12.             categories
13.         WHERE
14.             cat_id = " . mysql_real_escape_string($_GET['id']);
15.  
16. $result = mysql_query($sql);
17.  
18. if(!$result)
19. {
20.     echo 'The category could not be displayed, please try again later.' . mysql_error();
21. }
22. else
23. {
24.     if(mysql_num_rows($result) == 0)
25.     {
26.         echo 'This category does not exist.';
27.     }
28.     else
29.     {
30.         //display category data
31.         while($row = mysql_fetch_assoc($result))
32.         {
33.             echo '<h2>Topics in ′' . $row['cat_name'] . '′ category</h2>';
34.         }
35.      
36.         //do a query for the topics
37.         $sql = "SELECT  
38.                     topic_id,
39.                     topic_subject,
40.                     topic_date,
41.                     topic_cat
42.                 FROM
43.                     topics
44.                 WHERE
45.                     topic_cat = " . mysql_real_escape_string($_GET['id']);
46.          
47.         $result = mysql_query($sql);
48.          
49.         if(!$result)
50.         {
51.             echo 'The topics could not be displayed, please try again later.';
52.         }
53.         else
54.         {
55.             if(mysql_num_rows($result) == 0)
56.             {
57.                 echo 'There are no topics in this category yet.';
58.             }
59.             else
60.             {
61.                 //prepare the table
62.                 echo '<table border="1">
63.                       <tr>
64.                         <th>Topic</th>
65.                         <th>Created at</th>
66.                       </tr>'; 
67.                      
68.                 while($row = mysql_fetch_assoc($result))
69.                 {               
70.                     echo '<tr>';
71.                         echo '<td class="leftpart">';
72.                             echo '<h3><a href="topic.php?id=' . $row['topic_id'] . '">' . $row['topic_subject'] . '</a><h3>';
73.                         echo '</td>';
74.                         echo '<td class="rightpart">';
75.                             echo date('d-m-Y', strtotime($row['topic_date']));
76.                         echo '</td>';
77.                     echo '</tr>';
78.                 }
79.             }
80.         }
81.     }
82. }
83.  
84. include 'footer.php';
85. ?>

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:

1. SELECT
2.     topic_id,
3.     topic_subject
4. FROM
5.     topics
6. WHERE
7.     topics.topic_id = " . mysql_real_escape_string($_GET['id'])

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.

1. SELECT
2.     posts.post_topic,
3.     posts.post_content,
4.     posts.post_date,
5.     posts.post_by,
6.     users.user_id,
7.     users.user_name
8. FROM
9.     posts
10. LEFT JOIN
11.     users
12. ON
13.     posts.post_by = users.user_id
14. WHERE
15.     posts.post_topic = " . mysql_real_escape_string($_GET['id'])

The final topic view looks like this:

Step 12: Adding a Reply

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

1. <form method="post" action="reply.php?id=5">
2.     <textarea name="reply-content"></textarea>
3.     <input type="submit" value="Submit reply" />
4. </form>

The complete reply.php code looks like this.

1. <?php
2. //create_cat.php
3. include 'connect.php';
4. include 'header.php';
5.  
6. if($_SERVER['REQUEST_METHOD'] != 'POST')
7. {
8.     //someone is calling the file directly, which we don't want
9.     echo 'This file cannot be called directly.';
10. }
11. else
12. {
13.     //check for sign in status
14.     if(!$_SESSION['signed_in'])
15.     {
16.         echo 'You must be signed in to post a reply.';
17.     }
18.     else
19.     {
20.         //a real user posted a real reply
21.         $sql = "INSERT INTO 
22.                     posts(post_content,
23.                           post_date,
24.                           post_topic,
25.                           post_by) 
26.                 VALUES ('" . $_POST['reply-content'] . "',
27.                         NOW(),
28.                         " . mysql_real_escape_string($_GET['id']) . ",
29.                         " . $_SESSION['user_id'] . ")";
30.                          
31.         $result = mysql_query($sql);
32.                          
33.         if(!$result)
34.         {
35.             echo 'Your reply has not been saved, please try again later.';
36.         }
37.         else
38.         {
39.             echo 'Your reply has been saved, check out <a href="topic.php?id=' . htmlentities($_GET['id']) . '">the topic</a>.';
40.         }
41.     }
42. }
43.  
44. include 'footer.php';
45. ?>

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.
Written by:  Evert Padje

If you feel useful for you and for everyone, please share it!
Suggest for you:

The Complete PHP with MySQL Developer Course (New)

PHP MySQL Database Connections

Learning PHP 7: From the Basics to Application Development

The Complete PHP 7 Guide for Web Developers

Learn PHP 7 This Way to Rise Above & Beyond Competion!