Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.
Sign In
Not a member yet? Click here to register.

Need help modifying new posts file

Hi, I've got some skills in php and a rudimentary understanding of queries in MySQL but this is a little beyond my scope of understanding.
What I'd like to do is instead of new_posts.php showing all the new posts since the last visit is have it show only one new post per thread since the last visit. I assume it is easier to do the most recent post or hopefully one with MIN time since last visit.

The 2 queries that are apart of this PHP are:
[syntaxhighlighter brush=php,first-line=1,highlight=0,collapse=false,html-script=false]
$result = dbquery(
"SELECT tp.post_id FROM ".DB_POSTS." tp
LEFT JOIN ".DB_FORUMS." tf ON tp.forum_id = tf.forum_id
LEFT JOIN ".DB_THREADS." tt ON tp.thread_id = tt.thread_id
WHERE ".groupaccess('tf.forum_access')." AND tp.post_hidden='0' AND tt.thread_hidden='0' AND tp.post_datestamp > '".$lastvisited."'"
);
$rows = dbrows($result);

if ($rows) {
if (!isset($_GET['rowstart']) || !isnum($_GET['rowstart'])) { $_GET['rowstart'] = 0; }
$result = dbquery(
"SELECT tp.forum_id, tp.thread_id, tp.post_id, tp.post_author, tp.post_datestamp,
tf.forum_name, tf.forum_access, tt.thread_subject, tu.user_id, tu.user_name, tu.user_status
FROM ".DB_POSTS." tp
LEFT JOIN ".DB_FORUMS." tf ON tp.forum_id = tf.forum_id
LEFT JOIN ".DB_THREADS." tt ON tp.thread_id = tt.thread_id
LEFT JOIN ".DB_USERS." tu ON tp.post_author = tu.user_id
WHERE ".groupaccess('tf.forum_access')." AND tp.post_hidden='0' AND tt.thread_hidden='0' AND tp.post_datestamp > '".$lastvisited."'
ORDER BY tp.post_datestamp DESC LIMIT ".$_GET['rowstart'].",20"
);[/syntaxhighlighter]

I think it needs a DISTINCT tp.thread_id in there and something that compares the tp.post_datestamp of posts in the same thread.

I have gone looking for this pre-made in the past but haven't found one

Thanks to all who read && || help

P.S. I understand it'll need to be called new_threads.php to be consistent
P.P.S. New_threads.php sounds like clothing :P
P.P.S. Whole file is attached
Byro attached the following file:
new_posts.zip [1.5kB / 158 Downloads]
Thread Information
Author
Replies
1 post
Views
905 times
Last Post
Last updated on 9 years ago
You can view all discussion threads in this forum.
You cannot set up a bounty in this discussion thread.
You can start a new discussion thread in this forum.
You cannot reply in this discussion thread.
You cannot start on a poll in this forum.
You cannot upload attachments in this forum.
You can download attachments in this forum.
You cannot up or down-vote on the post in this discussion thread.
Users who participated in discussion: Byro