Page 1 of 1

Replied Threads

Posted: Thu Jul 09, 2009 8:10 am
by jackpf
Hi all,
I've made a forum...and I'm hoping to make a feature where the user can see all threads that have been replied to since they last viewed them.

I currently have an array of each thread id as the key, and the number of posts as its value.

So, I could easily do something like this:

Code: Select all

//in the while loop of retrieving threads
if($current_number_of_posts != $forum_data[$id])
{
continue;
}
However, this will mess up my pagination, and it will mean retrieving unecessary rows from the database.

I just can't think of how to do this with SQL.

Any ideas would be great.

Thanks,
Jack.

Re: Replied Threads

Posted: Fri Jul 10, 2009 3:54 pm
by andyhoneycutt
What is your layout that comparing the current number of threads to something else has an adverse effect on your pagination?

Also, perhaps I'm missing something: If you already have the current number of posts for the thread, in this line:

Code: Select all

if($current_number_of_posts != $forum_data[$id])
am I to assume that $forum_data[$id] is the previous post count for the forum?

Re: Replied Threads

Posted: Fri Jul 10, 2009 4:02 pm
by jackpf
Right...well, I currently do pretty normal pagination with LIMITs in queries. If I then start to continue; threads that I want to skip, I'll be getting pretty random numbers of threads per page...and it might not display the correct number of threads for each page.

That's why I've been trying to do it with SQL...so it will leave my pagination intact.

And yeah $forum_data will be an array of all threads and their post count, stored in a cookie from when the user last viewed the thread.

I just can't figure out how to compare the post count in the query with the post count in the cookie.

Thanks for the reply,
Jack.

Re: Replied Threads

Posted: Fri Jul 10, 2009 4:04 pm
by andyhoneycutt
I'm sorry, I promise I'm paying attention. I don't understand how if you're setting the value in the cookie, you don't know how to get it from the cookie to compare it...

Re: Replied Threads

Posted: Fri Jul 10, 2009 4:12 pm
by jackpf
Lol I believe you.

Ok, so I want to get all threads that have a post count greater than the post count recorded in the cookie.

Now, a cookie like this:

Code: Select all

array(
131 => 20,
100 => 10,
111 => 15
);
Will mean the user has seen threads 131 with 20 posts, 100 with 10, and 111 with 15.

My current sql is somethign like this: (dumbed down version)

Code: Select all

SELECT F.*, COUNT(P.`ID`) AS `PostCount`
FROM `Forum` F
INNER JOIN `Forum` P ON P.`Thread`=F.`ID`
GROUP BY F.`ID`
ORDER BY F.`ID` DESC
I need to some how only retrieve threads with `PostCount` greater than whatever their post count is in the cookie...

So for example,

Code: Select all

SELECT F.*, COUNT(P.`ID`) AS `PostCount`
FROM `Forum` F
INNER JOIN `Forum` P ON P.`Thread`=F.`ID`
WHERE `ID`=111
GROUP BY F.`ID`
HAVING `PostCount`>{$forum_data[111]}
ORDER BY F.`ID` DESC
Would suffice for one thread (with ID 111), as in, it would return the thread if it has new replies, but I need to do this for all threads...It's confusing the hell out of me.

Re: Replied Threads

Posted: Fri Jul 10, 2009 4:29 pm
by andyhoneycutt
I'm not entirely sure there is a very simple way to do this. I would recommend looping through $forum_data and performing the query for each one. You're not necessarily grabbing a post count for each thread, just the threads the user has viewed (stuff in the cookies, I would presume).

Re: Replied Threads

Posted: Fri Jul 10, 2009 5:04 pm
by jackpf
Yeah...but that would require either continuing irrelevant threads (which would mess up pagination) or run a query for every thread which would probably just kill me server.

I think you're right....there's probably not a decent way of doing this.

I guess I was just looking for clarification.

Ahh well, thanks for your time,
Jack.

Re: Replied Threads

Posted: Fri Jul 10, 2009 5:20 pm
by jackpf
AHA! I've just have a thought... (well, I noticed it while reading through SMF's source to see how they do it); I could create a temporary table with IDs and their postcounts from the cookie, and then base the query with a join from that.

It'll be slow as sh|t, but it might work...

Re: Replied Threads

Posted: Fri Jul 10, 2009 5:58 pm
by jackpf
Score...it worked :D

I just created a temporary table, filled it with the forum cookie data, and added this to the query:

Code: Select all

INNER JOIN `Forum_Cookie` C ON F.`ID`=C.`ID`
//AND
HAVING `PostCount`>`Forum_Cookie`
Beautiful. Thanks for the support :D Without your reply I would probably not have had another go at this...

And it's not even slow surprisingly. Can't complain... :)

Re: Replied Threads

Posted: Sun Jul 12, 2009 12:58 pm
by andyhoneycutt
Awesome man!! Thanks for following up with your solution. I am positive myself or others will be able to use your insight on this one in the future :)

-Andy