building a forum...need a bit of help.

jonaHill87 · November 15, 2008

Hi all. I'm building a small forum and it's almost finished but there's one thing I didn't take into consideration. I don't even know if this is possible the way I have my db set up so I will provide as much info as I can here.

What I'm trying to do:

I didn't consider how I want my index posts to be ordered. I want them to be ordered by date(whether its a new topic or a reply to a topic) so that the latest posting is the first index. (basically like any forum). Now I have no idea how to do this because I have a 'topics' db table and 'replies' db table that both have a date field and I need to compare those dates. But if there are no replies for a topic, I can't compare the dates.

Here's a stripped down version of my db tables:

Topics:

Field: topicId PK

Field: title

Field: memberId FK

Field: comment

Field: date/time

Replies:

Field: replyId PK

Field: topicId FK

Field: memberId FK

Field: comment

Field: date/time

So I can't figure out how this can be done with a single query or even multiple queries on both tables. Any help would be greatly appreciated as I'm about ready to give up.

ShiloVir · November 15, 2008

Heres a sample Forum. Modify to Fit your needs:

SQL Query:

CREATE TABLE forum_topics (
topic_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
topic_title VARCHAR (150),
topic_create_time DATETIME,
topic_owner VARCHAR (150)
};

CREATE TABLE forum_posts (
post_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
topic_id INT NOT NULL,
post_text TEXT,
post_create_time DATETIME,
post_owner VARCHAR (150)
);

Addtopic.html

<html>
<head>
<title>Add a Topic</title>
</head>
<body>
<h1>Add a Topic</h1>
<form method="post" action="do_addtopic.php">
<p><strong>Your E-Mail Address:</strong><br/>
<input type="text" name="topic_owner" size="40" maxlength="150"/></p>
<p><strong>Topic Title:</strong><br/>
<input type="text" name="topic_title" size="40" maxlength="150"/></p>
<p><strong>Post Text:</strong><br/>
<textarea name="post_text" rows="8" cols="40" wrap="virtual"></textarea></p>
<p><input type="submit" name="submit" value="Add Topic"></p>
</form>
</body>
</html>

do_addtopic.php

<?php
//check for required fields from the form
if ((!$_POST["topic_owner"]) || (!$_POST["topic_title"]) || (!$_POST["post_text"])) {
header("Location: addtopic.html");
exit;
}

//connect to server
$mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");

//create and issue the first query
$add_topic_sql = "INSERT INTO forum_topics (topic_title, topic_create_time,topic_owner) VALUES ('".$_POST["topic_title"]."',now(), '".$_POST["topic_owner"]."')";
$add_topic_res = mysqli_query($mysqli, $add_topic_sql) or die(mysqli_error($mysqli));

//get the id of the last query
$topic_id = mysqli_insert_id($mysqli);

//create and issue the second query
$add_post_sql = "INSERT INTO forum_posts (topic_id,post_text,post_create_time,post_owner) VALUES ('".$topic_id."', '".$_POST["post_text"]."', now(), '".$_POST["topic_owner"]."')";
$add_post_res = mysqli_query($mysqli, $add_post_sql) or die(mysqli_error($mysqli));

//close connection to MySQL
mysqli_close($mysqli);

//create nice message for user
$display_block = "<P>The <strong>".$_POST["topic_title"]."</strong> topic has been created.</p>";
?>
<html>
<head>
<title>New Topic Added</title>
</head>
<body>
<h1>New Topic Added</h1>
<?php echo $display_block; ?>
</body>
</html>

replytopost.php

<?php
//connect to server
$mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");

//check to see if we're showing the form or adding the post
if (!$_POST) {
// showing the form; check for required item in query string
if (!isset($_GET["post_id"])) {
	header("Location: topiclist.php");
	exit;
}

//still have to verify topic and post
$verify_sql = "SELECT ft.topic_id, ft.topic_title FROM forum_posts AS fp LEFT JOIN forum_topics AS ft ON fp.topic_id = ft.topic_id WHERE fp.post_id = '".$_GET["post_id"]."'";

$verify_res = mysqli_query($mysqli, $verify_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($verify_res) < 1) {
	//this post or topic does not exist
	header("Location: topiclist.php");
	exit;
} else {
	//get the topic id and title
	while($topic_info = mysqli_fetch_array($verify_res)) {
		$topic_id = $topic_info['topic_id'];
		$topic_title = stripslashes($topic_info['topic_title']);
	}

	echo "
	<html>
	<head>
	<title>Post Your Reply in ".$topic_title."</title>
	</head>
	<body>
	<h1>Post Your Reply in $topic_title</h1>
	<form method=\"post\" action=\"".$_SERVER["PHP_SELF"]."\">
	<p><strong>Your E-Mail Address:</strong><br/>
	<input type=\"text\" name=\"post_owner\" size=\"40\" maxlength=\"150\"></p>

	<p><strong>Post Text:</strong><br/>
	<textarea name=\"post_text\" rows=\"8\" cols=\"40\" wrap=\"virtual\"></textarea>

	<input type=\"hidden\" name=\"topic_id\" value=\"$topic_id\">
	<P><input type=\"submit\" name=\"submit\" value=\"Add Post\"></p>

	</form>
	</body>
	</html>";
}

//free result
mysqli_free_result($verify_res);

//close connection to MySQL
mysqli_close($mysqli);

} else if ($_POST) {
//check for required items from form
if ((!$_POST["topic_id"]) || (!$_POST["post_text"]) || (!$_POST["post_owner"])) {
	header("Location: topiclist.php");
	exit;
}

//add the post
$add_post_sql = "INSERT INTO forum_posts (topic_id,post_text,post_create_time,post_owner) VALUES ('".$_POST["topic_id"]."', '".$_POST["post_text"]."', now(), '".$_POST["post_owner"]."')";
$add_post_res = mysqli_query($mysqli, $add_post_sql) or die(mysqli_error($mysqli));

//close connection to MySQL
mysqli_close($mysqli);

//redirect user to topic
header("Location: showtopic.php?topic_id=".$_POST["topic_id"]);
exit;
}
?>

showtopic.php

<?php
//check for required info from the query string
if (!isset($_GET["topic_id"])) {
header("Location: topiclist.php");
exit;
}

//connect to server
$mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");

//verify the topic exists
$verify_topic_sql = "SELECT topic_title FROM forum_topics WHERE topic_id = '".$_GET["topic_id"]."'";
$verify_topic_res =  mysqli_query($mysqli, $verify_topic_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($verify_topic_res) < 1) {
//this topic does not exist
$display_block = "<p><em>You have selected an invalid topic.<br/>
Please <a href=\"topiclist.php\">try again</a>.</em></p>";
} else {
//get the topic title
while ($topic_info = mysqli_fetch_array($verify_topic_res)) {
	$topic_title = stripslashes($topic_info['topic_title']);
}

//gather the posts
$get_posts_sql = "SELECT post_id, post_text, DATE_FORMAT(post_create_time, '%b %e %Y at %r') AS fmt_post_create_time, post_owner FROM forum_posts WHERE topic_id = '".$_GET["topic_id"]."' ORDER BY post_create_time ASC";
$get_posts_res = mysqli_query($mysqli, $get_posts_sql) or die(mysqli_error($mysqli));

//create the display string
$display_block = "
<p>Showing posts for the <strong>".$topic_title."</strong> topic:</p>
<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\">
<tr>
<th>AUTHOR</th>
<th>POST</th>
</tr>";

while ($posts_info = mysqli_fetch_array($get_posts_res)) {
	$post_id = $posts_info['post_id'];
	$post_text = nl2br(stripslashes($posts_info['post_text']));
	$post_create_time = $posts_info['fmt_post_create_time'];
	$post_owner = stripslashes($posts_info['post_owner']);

	//add to display
 	$display_block .= "
	<tr>
	<td width=\"35%\" valign=\"top\">".$post_owner."<br/>[".$post_create_time."]</td>
	<td width=\"65%\" valign=\"top\">".$post_text."<br/><br/>
	<a href=\"replytopost.php?post_id=".$post_id."\"><strong>REPLY TO POST</strong></a></td>
	</tr>";
}

//free results
mysqli_free_result($get_posts_res);
mysqli_free_result($verify_topic_res);

//close connection to MySQL
mysqli_close($mysqli);

//close up the table
$display_block .= "</table>";
}
?>
<html>
<head>
<title>Posts in Topic</title>
</head>
<body>
<h1>Posts in Topic</h1>
<?php echo $display_block; ?>
</body>
</html>

topiclist.php

<?php
//connect to server
$mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");

//gather the topics
$get_topics_sql = "SELECT topic_id, topic_title, DATE_FORMAT(topic_create_time,  '%b %e %Y at %r') aS fmt_topic_create_time, topic_owner FROM forum_topics ORDER BY topic_create_time DESC";
$get_topics_res = mysqli_query($mysqli, $get_topics_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($get_topics_res) < 1) {
//there are no topics, so say so
$display_block = "<p><em>No topics exist.</em></p>";
} else {
//create the display string
$display_block = "
<table cellpadding=\"3\" cellspacing=\"1\" border=\"1\">
<tr>
<th>TOPIC TITLE</th>
<th># of POSTS</th>
</tr>";

while ($topic_info = mysqli_fetch_array($get_topics_res)) {
	$topic_id = $topic_info['topic_id'];
	$topic_title = stripslashes($topic_info['topic_title']);
	$topic_create_time = $topic_info['fmt_topic_create_time'];
	$topic_owner = stripslashes($topic_info['topic_owner']);

	//get number of posts
	$get_num_posts_sql = "SELECT COUNT(post_id) AS post_count FROM forum_posts WHERE topic_id = '".$topic_id."'";
	$get_num_posts_res = mysqli_query($mysqli, $get_num_posts_sql) or die(mysqli_error($mysqli));

	while ($posts_info = mysqli_fetch_array($get_num_posts_res)) {
		$num_posts = $posts_info['post_count'];
	}

	//add to display
	$display_block .= "
	<tr>
	<td><a href=\"showtopic.php?topic_id=".$topic_id."\"><strong>".$topic_title."</strong></a><br/>
	Created on ".$topic_create_time." by ".$topic_owner."</td>
	<td align=center>".$num_posts."</td>
	</tr>";
}
//free results
mysqli_free_result($get_topics_res);
mysqli_free_result($get_num_posts_res);

//close connection to MySQL
mysqli_close($mysqli);

//close up the table
$display_block .= "</table>";
}
?>
<html>
<head>
<title>Topics in My Forum</title>
</head>
<body>
<h1>Topics in My Forum</h1>
<?php echo $display_block; ?>
<p>Would you like to <a href="addtopic.html">add a topic</a>?</p>
</body>
</html>

Of course this isnt secure and shouldnt be trusted to work on. u will need to add some security features and add some sort of login system.

jonaHill87 · November 15, 2008

Whoa, that a lot of code. Ok thanks I'll go through this all of this and post back if I have any problems. btw, what's 'mysqli'? I haven't seen that before.

ShiloVir · November 15, 2008

Whoa, that a lot of code. Ok thanks I'll go through this all of this and post back if I have any problems. btw, what's 'mysqli'? I haven't seen that before.

PHP.NET - MySQLi

Yah. I made a few Edits with the SQL Query. So if u get any problems drop your database and resend the SQL query. if ur still getting a problem. then post back :-\

PS: Dont drop ur database if theres other stuff on it. I suggest making a new database to try this code out

jonaHill87 · November 15, 2008

alright. thanks bud

jonaHill87 · December 3, 2008

Sorry it took me so long to report back but I've been busy. Anyway, thanks for sharing these pages but they do not

solve my problem. I want to order my topics from newest to oldest (by post date) so that when some one posts inside of a topic, that topic will be at the top of the topic list. I changed my database to the database you provided above because it's easier to work with then my original db.

The closest I have come to figuring this out is with this query but it will only pick up the first entry of the topic_id and skip later post entries. I cant get it to pick only the latest post date for that topic_id. I hope that makes sense.

select distinct t.topic_id, t.topic_title

from forum_posts p, forum_topics t

where p.topic_id = t.topic_id

order by p.post_create_time desc

premiso · December 3, 2008

select t.topic_id, t.topic_title, p.post_id
from forum_posts p, forum_topics t
where p.topic_id = t.topic_id
order by p.post_create_time desc

That should pick up the topics. You were limiting yourself by not including the post_id and using the distinct.

That would return x rows depending on how many posts there are for a topic so you can show all the posts by the id.

Hope that helps.

jonaHill87 · December 4, 2008

You're right I do need the post_id as well, but, the reason I tried using distinct is because I don't want the same topic_id to be displayed more than once. Let me see if I can explain this a bit better. I have 5 records(topics) in my forum_topics table and I have 8 records(posts) in my forum_posts table. So obviously one of my topics has more than 1 post. In my query, I want to display only the 5 records(topics) with each containing the post_id of the newest post(post_create_time). Your query gives me all 8 records. I'm beginning to think this is impossible. Any more help would be greatly appreciated.

jonaHill87 · December 5, 2008

bump. any ideas anyone?

gevans · December 5, 2008

select t.topic_id, t.topic_title, p.post_id
from forum_posts p, forum_topics t
where p.topic_id = t.topic_id
order by p.post_create_time desc
limit 5

I didn't read everything, but from what I can see you just want to limit your query

jonaHill87 · December 7, 2008

no dice man. gives me the same results as previous but limits to 5 records.

Sign In

building a forum...need a bit of help.

Recommended Posts

jonaHill87

Link to comment

Share on other sites

ShiloVir

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

ShiloVir

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

premiso

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

gevans

Link to comment

Share on other sites

jonaHill87

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information