Creating a simple forum

Id like to create a very simplistic message board, so I gather I need two tables,

CREATE TABLE messages(
mess_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id MEDIUMINT UNSIGNED NOT NULL,
topic_id TINYINT UNSIGNED NOT NULL,
parent_id INT UNSIGNED NOT NULL,
subject VARCHAR(50) NOT NULL,
message_txt LONGTEXT NOT NULL,
date TIMESTAMP NOT NULL,
parent_id INT UNSIGNED NOT NULL,
PRIMARY KEY (mess_id)
);

CREATE TABLE topics(
topic_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
topic VARCHAR(25),
PRIMARY KEY (topic_id)
);

Since 1 message can only have 1 topic but 1 topic can have many messages, is the tables structure set up correctly/ Do I need to link them like

FOREIGN KEY (topic_id) REFERENCES topics (topic_id`),

thanks…

You are aware that TINYINT can go only to 255 and you’re OK with that?

The primary key on the messages table should be (topic_id,mess_id) so as to keep the messages for each topic together.

Also you have two parent_id fields in the messages table.

[quote=“felgall, post:3, topic:101982, full:true”]… so as to keep the messages for each topic together.[/quote]this is hardly a good reason for a compound PK and subordinate auto_increment

it was to be a “very simplistic” message board, not one that puts a focus on clustering indexes right from the get-go before a need has been shown

your note that parent_id occurs twice was correct, but you missed the fact that it would also have to be a compound FK, wouldn’t it

Yes, this will be a very small board (I’ll only have like a dozen topics so TINYINT is ok) Ill get rid of the duplicate parent_id. but what is a compound foreign key? my thinking on the parent_id is that if it is set (NOT NULL) it is a reply to another message

Actually, can someone explain to me how to use parent_id to differentiate a post/message to its reply?
do I make the parent_id null by default but if its a response, make the parent_id the mess_id of the message?

[quote=“lukeurtnowski, post:6, topic:101982, full:true”]
do I make the parent_id null by default but if its a response, make the parent_id the mess_id of the message?
[/quote]yes, exactly

thanks

raan into my first stumbling block, my simple form is starting to take shape,
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
On this page, you can see the topic, from the URL and the messages (from the messages table)
How do I place eqach messages reply inside nth div
heres the php to see each message,

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);     

try {

$stmt = $dbh->prepare('SELECT * FROM `topics`');
$stmt->execute();

while($row = $stmt->fetch()) {

echo "<a class='list-group-item' href='category.php?id=".$row['topic_id']."&topic=".$row['topic']."'><h4 class='list-group-item-heading'>".$row['topic']."</h4><p class='list-group-item-text'>".$row['t_desc']."</p></a>";
}
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}

also, how would I show the author for all the messages? (I have the user_id for each message but the users name is in the users table)

Thanks…

[quote=“lukeurtnowski, post:11, topic:101982, full:true”]
also, how would I show the author for all the messages? (I have the user_id for each message but the users name is in the users table)[/quote][voice type=“darthvader”]
use a join, luke
[/voice]

2 Likes

Obligatory image :smile:

3 Likes

ok am a little new to thsese so bear with me,
my current query is

SELECT * FROM `messages` WHERE `topic_id` = :id

But I should be doing…

SELECT messages.subject, messages.message_txt, messages,mess_id, messages.parent_id, messages.created, users.name FROM `messages` INNER JOIN `users` ON messages.user_id = users.id

Is that all im allowed to do with thew JOIN (or am I allowed to add WHERE messages.topic_id = :id)? like

SELECT messages.subject, messages.message_txt, messages.mess_id, messages.parent_id, 
messages.created, users.name FROM `messages` 
INNER JOIN `users` ON messages.user_id = users.id
WHERE messages.topic_id = :id

Ill be…
it worked
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
Im trying to count the replies for each message as well as display them, how can I do that.

Thanks

[quote=“lukeurtnowski, post:15, topic:101982, full:true”]
Im trying to count the replies for each message as well as display them, how can I do that.[/quote]with another join

this time you’re joining back to the same table, so both of them should have aliases

SELECT mess.subject , mess.message_txt , mess.mess_id , mess.parent_id , mess.created , users.name , COUNT(rply.parent_id) AS replies FROM messages AS mess INNER JOIN users ON users.id = mess.user_id LEFT OUTER JOIN messages AS rply ON rply.parent_id = mess.mess_id WHERE mess.topic_id = :id AND mess.parent_id IS NULL GROUP BY mess.mess_id

thanks for your help, I made some progress,
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
I use this method to cut the message text (in case of a long message)

echo "<div class='panel-body'>".substr($row['message_txt'],0,85)."<a href=''>[more]</a></div>";

How do I make is so that when the [more] link is clicked, the rest of the message is displayed?

http://www.webdeveloper.com/forum/showthread.php?236997-Creating-a-quot-Read-More-quot-link

ok, it looks somewhat better,
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
but how do I print all the replies to the first message inn a

underneath all the blahs’?
im guessing il use a foreach loop somehow

echo "<div class='panel-body comment more'>".$row['message_txt'];
echo "</div>";
//foreach code

I guess a while loop is best for this, heres what I have soo far

while() {
echo "<blockquote><p>".$row['rply']."</P>";
echo "<footer>".$row['name']." on ".$row['created']."</footer>";
echo "</blockquote>";
}

Because I want to loop through each message (rply) im having trouble figuring out what to put as the condition in the while loop

Heres the effect I’m going for,
http://www.phpjabbers.com/preview/stiva-forum/index.php?ac=loadQuestion
I cant figure out how they did that (when you click on the reply link, the page doesnt seem to refresh, only ac=loadReply&qid=4 is appended to the URL).
Attached is the messages table, so this is where I need to pull the message_text, subject, created from (as well as the user which requires an inner join). Heres the original query,

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name,
COUNT(rply.parent_id) AS replies
FROM messages AS mess
INNER JOIN `users` 
ON users.id =  mess.user_id
LEFT OUTER JOIN messages AS rply
ON rply.parent_id = mess.mess_id
WHERE mess.topic_id = 1
AND mess.parent_id IS NULL
GROUP BY mess.mess_id

and I think the new query would be

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name,
FROM messages AS mess
INNER JOIN `users` 
ON users.id = mess.user_id
WHERE mess.parent_id = 3
GROUP BY mess.created

(the above query results in an error (screenshot))
Why doesnt the query show the replies:?