Replied Threads

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Replied Threads

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Replied Threads

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Replied Threads

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Replied Threads

Post 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...
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Replied Threads

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Replied Threads

Post 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).
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Replied Threads

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Replied Threads

Post 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...
Last edited by jackpf on Fri Jul 10, 2009 8:25 pm, edited 1 time in total.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Replied Threads

Post 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... :)
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Replied Threads

Post 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
Post Reply