Threaded Discussion with PHP/MySQL

Please note: we were unable to find the original author of this tutorial. If you are the author and would like some acknowledgement and/or a link to your home page please contact us

I thought I'd take a few minutes to show what these tools can do once you put them to work. Using the code I have here, you can add a threaded discussion area to your site.

I'm using MySQL, but be aware that PHP supports nearly every database I've ever heard of and a few I haven't. So if you've got something else already installed, use that. The PHP functions are named similarly and do similar things — almost as if someone planned it that way. Be warned, though, that the database systems themselves aren't much alike. Sybase, Oracle, Postgres, MySQL, and mSQL are all good relational databases — they can all use SQL — but once you get away from the basic functions, they work a bit differently. This is a good thing. It's more likely that one of them will fit your needs. Just don't count on, say, outer joins working the same way from one to the other.

Having covered myself here, let's move on to the code.

Code Overview

First we'll need to set up a table for topics. Each topic has a title, an author, and some comments. A topic can be brand-spanking-new, or it can be a reply to some previous topic — or a reply to a reply, etc.

I created a table named px_topics in the test database: 

create table px_topics  ( topic_id  integer not null auto_increment,            
arent_id  integer default 0,  
root_id  integer default 0,          
name  varchar(255),            
description   text null,          
create_dt  timestamp,            
odify_dt  timestam
uthor   varchar(255) nul
uthor_host  varchar(255) null,
ey px_topics_key (topic_i);

Tere are a couple of things in the above statement I should discuss. The qualifier auto_increment tells MySQL to generate a unique value 1 greater than the previous value for the topic_id column. The user can override auto_increment by specifying a value. This works because I've identified topic_id as the primary key in the key clause at the end of the table definition. This is a handy feature for lazy example writers. But if you were building a system anticipating a lot of entries — tens of thousands a day — you'd want to consider building your own keys.

The code is broken up into four files: display_topic.phtml, write_topic.phtml, dbconnect.phtml, and functions.phtml. They do what their names imply: display_topic.phtml displays a topic or the top-level list of topics if you don't specify one, write_topic.phtml lets you create a topic, and functions.phtml ... oh, wait, we haven't done functions yet, have we?

Just like any other language, PHP lets you gather code you're going to use over and over into a reusable bit called a function. The syntax is simple: 

function MyFunction ($argument, $other_argument="default value")    
{# your code here — reasonable rates — call now    }
A couple of points of interest: If you don't give a default value to an argument and you fail to specify a value when you call the function, you'll get an error. With PHP, you always have to include the argument parentheses after the function name, even if they're empty (i.e., there are no arguments to the function).

I've found that it's helpful to gather my functions into a single file. This lets the other files deal with HTML display and the high-level logical flow of the code. The user will start out looking at a list of top-level topics, the ones that aren't related to any that came before them. When a topic is selected, a reader will be able to see the titles of all the replies to the original message, the replies to those replies, and so on. The title of each topic will serve as a link to a complete display of that topic. Take a look at the example if this isn't clear.

The user will first pull up display_topic.phtml. To make the URL easier to remember, I set up the index file of the directory to include this file. Since the user is just starting out, no topic will have been specified.

Here's the important bit from this file: 

<?php if (empty($topic_id)) { $topic_id = 0; } DisplayTopic($topic_id);    ?>
Even though the DisplayTopic function is set up to assign a default value of zero to the topic_id argument, we still have to test that it has been set here. Otherwise we'd produce an error when calling DisplayTopic.

When DisplayTopic is called with a topic_id of zero, it will display the top-level topics. Here's the query: 

select distinct t.topic_id, t.parent_id, t.root_id, t.name, t.description    	
, t.author, t.author_host, t.create_dt, t.modify_dt     from px_topics t, px_topics r    
where t.topic_id = r.root_id
You may have noticed that we have only one table, yet we're performing a join. In the from clause I've created two instances of the table named t and r. Then in the where portion, I've joined the two tables so that the topic and the root are the same, assuring that we only end up with top-level topics. It's a little twisty but pretty simple.

The function goes on to create some HTML that will look something like this: 

<a href="display_topic.phtml?topic_id=1">my first topic</a><br>    
<a href="display_topic.phtml?topic_id=7">my second topic</a><br>    
<a href="display_topic.phtml?topic_id=15">my third topic</a><br>    
<a href="display_topic.phtml?topic_id=17">my fourth topic</a><br>
<a href="display_topic.phtml?topic_id=21">What should we have for lunch?</a><br>    
<a href="display_topic.phtml?topic_id=18">Let's get out of here.</a><br>

The live example isn't going to match what we have here. But let's assume that what we have here is what's in the database.

Topic Details

Let's say we want to see the replies to the "what should we have for lunch" topic. We'll click on the topic and call display_topic.phtml. In the process, we'll pass the topic number (here it's 21) to the querystring. In the DisplayTopic function we end up running the following query: 
select distinct t.topic_id, t.parent_id, t.root_id, t.name	,
t.description, t.author, t.author_host, t.create_dt, t.modify_dt,    
name  r.name  rom px_topics t
lefft join px_topics p on t.parent_id = p.topic_id

left join px_topics as r on t.root_id = r.topic_id     
where t.topic_id = 21
Once again, we've created a couple of instances of our table, but this time we're performing an outer join. So what's an outer join? The basic equi-join (which Jay covered a while back) joins two tables based on an equality in specified fields. And that's good. But here we want to join the tables based on equality and also get all the rows from one instance that have no match in the other two. In this example, we want all the rows from t whether or not there are any matches to parent_id or root_id.

For instance, if this were an equi-join and the t.parent_id field were null, the query would return no results, even though two out of the three matches work. An outer join lets you get around that problem.

For a little more information on joins, check out the MySQL documentation. If you're still confused, try running the query as an equi-join and then as an outer join. You'll see the difference.

We fetch information about topic 21 along with the name of its parent topic (if any) and its root topic (if any). This topic is, in fact, its own parent and root, so no information about them is displayed on the page. Only the title, author, date, and contents of the topic are printed. It will look like this:

topic list | new topic

What should we have for lunch? by Mr. Rogers (206.221.192.49) on 19990312185429

Let's not tell King Friday we're going.

Reply to this

Comments:

Re: What should we have for lunch? by King Friday ( 22 )
  Tell him what?

Here the user is invited to reply to the topic, and the existing replies are listed. The list is produced by calling another PHP function inside DisplayTopic named DisplayKids. At first, it fetches the topics that are direct replies to topic 21: 

select topic_id, name, author, create_dt, description  from px_topics where parent_id = 21     
order by create_dt, topic_id
Then for each reply, DisplayKids displays its title and then calls itself, getting the replies to that reply: 
select topic_id, name, author, create_dt, description  from px_topics where parent_id = 23    
order by create_dt, topic_id

It continues on down the line. Each reply is displayed in a different style with a different amount of information depending on its level.

Now let's pretend I had clicked on topic 23 above.

The Reply

For topic 23, things are going to be a little different. This is a reply to an existing topic, so we need to display links to the root-level topic and the immediate parent. It's all the same code, but we get different behavior because we have more information to work with.

Here's what you'll see:

topic list | new topic

root: What should we have for lunch?

parent: Re: What should we have for lunch?

Re: Re: What should we have for lunch? by Mr. Rogers (206.221.192.49) on 19990316234758

Um, your library book is overdue! We didn't want to upset you.

Reply to this.

Comments:

Uh oh! by King Friday ( 24 )
  I'd better go return it right now!

Let's finish up by showing what will happen if we click on "Reply to this." for topic 23. We call write_topic.phtml, and that brings up a form where the user can enter a response.

topic list | new topic

What should we have for lunch? by Mr. Rogers (206.221.192.49) on 19990312185429

Let's not tell King Friday we're going.

Care to comment?

Topic Subject:
Your Name:

Your Words Here:

This form displays the topic we're replying to, but the $nokids parameter of the DisplayTopic function keeps the form from displaying the entire chain of replies. The user fills out the form and clicks on Create. That calls write_topic.phtml again; this time it sees that one of the parameters is Create and calls the CreateTopic function. That inserts a record into the px_topics table: 
insert into px_topics  (name, description, parent_id, root_id , author, author_host)  values  
('Re: What should we have for lunch?', 'How about sushi?', 21 , 21 , 'lex luthor', '206.221.192.49' )
Then write_topic.phtml calls DisplayTopic on the newly created topic, and we already know all about that.

This may not be the exact code you want to use, but the basic idea is there. And this is a relatively trivial amount of code. That's what makes PHP so useful: You don't have to write too much, and testing a fix is simply a matter of reloading the page in your browser.

The syntax has its quirks, but they should be familiar to anyone who's worked in JavaScript, Perl, C, or the like.

Now you know it can be done. The only thing left is to go play with it and see what you think. So go. Go on. Git. Shoo!