PHP Created Forums Question

[Scropion]

I have created a forum that is fully functional but I cannot figure out how to make if their is topic that has a new post or reply to it. I can do it like for the forum but I cannot figure a way to do it for the new post.

 

<?php
$result1 = mysql_query("SELECT * FROM views WHERE thread_id = '$rows[last_post_thread]' and trainer_id='$info[id]' order by id desc limit 1") or die(mysql_error()); 
$result2 = mysql_query("SELECT * FROM views WHERE post_id = '$rows[last_post_reply]' and trainer_id='$info[id]' order by id desc limit 1") or die(mysql_error()); 
?>

 

The table view has

POST_ID check the lastest post number user has seen

Thread_ID same as POST_ID except thread

Trainer_ID = user_id

 

The rows variable is from the forum table that have

latest_thread inside the forum

lastest_post number inside the forum..

 

can anyone help me with this?

[Scropion]

I have created a forum that is fully functional but I cannot figure out how to make it check if their is topic that has a new post or reply to it. I can do it like for the forum but I cannot figure a way to do it for the new post.

 

<?php
$result1 = mysql_query("SELECT * FROM views WHERE thread_id = '$rows[last_post_thread]' and trainer_id='$info[id]' order by id desc limit 1") or die(mysql_error()); 
$result2 = mysql_query("SELECT * FROM views WHERE post_id = '$rows[last_post_reply]' and trainer_id='$info[id]' order by id desc limit 1") or die(mysql_error()); 
?>

 

The table view has

POST_ID check the lastest post number user has seen

Thread_ID same as POST_ID except thread

Trainer_ID = user_id

 

The rows variable is from the forum table that have

latest_thread inside the forum

lastest_post number inside the forum..

 

can anyone help me with this?

 

I couldn't edit button sorry guys...

[Scropion]

Anyone?

[trq]

Your original post makes little sense.

[Stephen]

I agree with thorpe...

 

Have you already implemented a commenting system and want to be able to somehow tell the forum that a new reply has been posted, or are you having trouble creating a script that allows users to reply to threads?

[joel24]

you'd have to have a table called say threadViews like so

userID

forumID

viewTime

 

and then each time a user views a thread, it updates that field.. or creates it if its non-existant... then to see if a thread has new posts you'd use a join like

SELECT forumID FROM threadViews t JOIN forumPosts f ON t.forumID = f.forumID WHERE last_post_reply > viewTime

 

[Scropion]

Alright, I'll try my hardest to say this but it's confusing to me.

 

I have a forum just like the one we are posting on that I created and it has everything like this. But I keep getting these complains by my members that they cannot tell if there is a NEW reply in their thread. They can tell if there is a NEW thread in the same forum but cannot tell if there is a reply in their thread.

 

I have tried many different ways to figure out a way but it's too hard and it's starting to hurt my head..

 

Let me know if this makes more sense or you need me to show you the tables.

[joel24]

read my post.

you'll need to put in a threadViews table

userID

forumID

viewTime (datetime)

where userID and forumID are both primary & foreign keys.

 

each time a user views a page have this sql statement run

INSERT INTO threadViews (userID, forumID, viewTime) VALUES (userID, forumID, NOW())
  ON DUPLICATE KEY UPDATE viewTime = NOW();

obviously change userID and forumID to $_SESSION['id'] or however you store those values.

 

then to check for threads with new posts

not exactly sure how your tables are set up... but say you have threadViews monitoring when a user views a thread, forumPosts which stores all the posts and forumTopics which stores all the topics

SELECT forumID, forumName, etc FROM threadViews t
JOIN forumPosts f ON t.forumID = f.forumID
JOIN forumTopics ft ON t.forumID = ft.forumID
WHERE last_post_reply > viewTime 
ORDER BY last_post_reply DESC

 

... thats assuming last_post_reply is a datetime field?

if not put in a datetime field for each thread which is updated everytime a reply etc is made to it.