Contact | Home PageDesigner | Coder tutorials

Database

Below are the database tables that are required for this code:


CREATE TABLE  `main` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  `lastposter` varchar(200) NOT NULL,
  `lastpostdate` int(11) NOT NULL,
  `topics` int(11) NOT NULL default '0',
  `replies` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
)

CREATE TABLE `replies` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `topicid` text NOT NULL,
  `message` text NOT NULL,
  `subject` text NOT NULL,
  `poster` text NOT NULL,
  `date` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE  `topics` (
  `id` int(11) NOT NULL auto_increment,
  `forumid` text NOT NULL,
  `message` text NOT NULL,
  `subject` text NOT NULL,
  `poster` text NOT NULL,
  `date` int(11) NOT NULL,
  `lastposter` varchar(200) NOT NULL,
  `lastpostdate` int(11) NOT NULL,
  `replies` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
)

Full code - forum.php

<html>
<head>
<title>Forum</title>
</head>
<body>
<table border="1" cellpadding="4" width="100%">
<tr>
<td>Forum</td>
<td>Number of Topics</td>
<td>Number of Replies</td>
…

Download the code »

Tutorial

This simple forum will consist of three elements. The first will be a list of forums, then each forum will have a list of topics and finally each topic will have a number of replies. We firstly need to set up the list of forums, these can be manually added to the database.

mysql_connect('localhost','user','password');
mysql_select_db('db');
$query1 = mysql_query('SELECT * FROM main ORDER BY id DESC');

This code connects to the database and selects the forums. We now need to display the information.

while ($output1 = mysql_fetch_assoc($query1))

This loop will get all the rows from the database and the loop will end when there are no rows left. Below is an example of getting the information from the database and printing it to the document.

echo '<td><a href="topics.php?id='.$output1['id'].'">'.$output1['name'].'</a></td>';

Full code - topics.php

<?php
ob_start();
$id = (int) $_GET['id'];
if ($id < 1)
{
	header('Location: forum.php');
	exit();
}
ob_end_clean();
?>
…

Download the code »

Topics

Now that the basic forums are set up we can list the topics for each one. The link from the main page sets an id variable that we can use to select the appropriate topics.

ob_start();
$id = (int) $_GET['id'];
if ($id < 1)
{
	header('Location: forum.php');
	exit();
}
ob_end_clean();

We start output buffering (ob_start()) to ensure that no headers are sent because if the topic id given is less than one we will redirect the user to the main forum page.

mysql_connect('localhost','user','password');
mysql_select_db('db');
$output1 = mysql_fetch_assoc(mysql_query("SELECT name FROM main WHERE id = $id"));
echo $output1['name'];

Now that we have an id that is greater than one we can get all the topics that match that it. We firstly get the forum name associated with the id and print it out.

$query2 = mysql_query("SELECT * FROM topics WHERE forumid = '$id' ORDER BY id DESC");
$query3 = mysql_num_rows($query2);

Within the database table design for the topics there is a foreign key (called forumid) which links each topic to the id of the forum.

if ($query3 == 0)
	echo '<td colspan="5">No Topics</td>';

If there are no topics for this forum then we will inform the user. Otherwise a table is built with links to the replies for each forum.

while ($output2 = mysql_fetch_assoc($query2))

We now need to get all the topics from the database. We will use mysql_fetch_assoc because we will refer to the fields in the array by name rather then by number.

echo '<td><a href="replies.php?id='.$output2['id'].'">'.$output2['subject'].'</a></td>';

Above is an example of the output sent to the user. It links the topics to the replies page with the appropriate id to display the replies relating to the topic.

if(empty($output2['lastposter']))
	echo '<td colspan="2">No replies</td>';

This forum example will keep track of the last poster for each forum and topic. If there are no posts a message is displayed otherwise the name and date of the last post is printed.

<form name="form1" id="form1" method="post" action="forumpost.php?type=topics&id=<? echo $id; ?>">

Finally on this page is the form that the user can use to post a new topic. If sends to pieces of information to the forum post page, the id of the topic and the type of post (in this case 'topic').

Full code - replies.php

<?php
ob_start();
$id = (int) $_GET['id'];
if ($id < 1)
{
	header('Location: forum.php');
	exit();
}
ob_end_clean();
?>
…

Download the code »

Replies

This page displays the replies for each topic. It is very similar to the topic page. It gets an id as an input which it uses to get all the replies. Each reply has a foreign key (topicid) which links to a particular topic and this is used to get all the relevant replies.

<form name="form1" id="form1" method="post" action="forumpost.php?type=replies&id=<? echo $id; ?>">

The form is also very similar to the topics page, but the type is now set to 'replies' as this form can only post new replies for each topic.

Full code - forumpost.php

<?php
ob_start();
$id = (int) $_GET['id'];
$type = $_GET['type'];
if ($id < 1 || ($type != 'replies' && $type != 'topics'))
{
	header('Location: forum.php');
	exit();
}
ob_end_clean();
…

Download the code »

Forum posting

Now that we have have set up the pages for the forum, topics and replies we need a page that will post the new topics and replies into the database.

ob_start();
$id = (int) $_GET['id'];
$type = $_GET['type'];
if ($id < 1 || ($type != 'replies' && $type != 'topics'))
{
	header('Location: forum.php');
	exit();
}
ob_end_clean();

This code checks that the id is greater than one and that the type given is one of the two constants that we defined (topics or replies).

function clear($message)
{
	if(!get_magic_quotes_gpc())
		$message = addslashes($message);
	$message = strip_tags($message);
	$message = htmlentities($message);
	return trim($message);
}

We now define a function to check the user input for any HTML tags, and remove any quotes, so that the information can be put into the database.

if($type == 'topics')
{
	$query = mysql_fetch_assoc(mysql_query("SELECT topics FROM main WHERE id = '$id'"));
	$topics = $query['topics'] + 1;
	mysql_query("UPDATE main SET topics = '$topics', lastposter = '$poster', lastpostdate = '$date' WHERE id = '$id'");
	mysql_query("INSERT INTO topics (id , forumid , message , subject, poster, date, lastposter, lastpostdate, replies)
              VALUES ('', '$id', '$message', '$subject','$poster', '$date', '', '', '0')");
	echo 'Topic Posted.<a href="topics.php?id='.$id.'">View Topic</a>';

If the type of post is a topic then we need to update multiple rows in the database. We firstly need to update the topics field of the forum to add one to the number of topics. We do this by using the id input to get the forum and the current number of topics. One is then added and the forum is updated (the current poster is also added as the latest poster and the last post date is updated). Finally the topic is added to the database.

else
{
	$query = mysql_fetch_assoc(mysql_query("SELECT replies, forumid FROM topics WHERE id = '$id'"));
	$replies = $query['replies'] + 1;
	$id2 =  $query['forumid'];
	mysql_query("UPDATE topics SET replies = '$replies', lastposter = '$poster', lastpostdate = '$date' WHERE id = '$id'");
	$query = mysql_fetch_array(mysql_query("SELECT replies FROM main WHERE id = '$id2'"));
	$replies = $query['replies'] + 1;
	mysql_query("UPDATE main SET replies = '$replies', lastposter = '$poster', lastpostdate = '$date' WHERE id = '$id2'");
	mysql_query("INSERT INTO replies (id , topicid, message, subject, poster, date)
              VALUES ('', '$id', '$message', '$subject','$poster', '$date')");
	echo 'Reply Posted.<a href="replies.php?id='.$id.'">View Reply</a>';

If the type of post is a reply then we need to do a similar database update but this time we need to add one to the number of replies. Using the id input we get the number of replies and the forum id from the related topic. We then update the number of replies and update the last poster and last post date fields to the current poster. Using the forum id we then also update the forum so that the last poster / last post date is updated and a total number of replies is kept. Finally the reply is added to the database.

Adobe Fireworks® Adobe Flash® and Adobe Photoshop® are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and/or other countries.
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

Home page • Designer • Coder • Robouk • Contact