October 25th, 2000, 11:52 AM
I've been trying to figure out a query for the message board on my site, but I've yet to be able to wrap my head around it.
Here's how it works right now. The query gets all of the topic messages within the last 7 days, and all the replies to those messages. The way it knows it's a topic is that the field "isreplyto" is set to zero. all of the replies have "isreplyto" set to the id number of the original.
What I'd like it to do, and I can't figure this out yet, is to have it check to see if there's been replies within the last seven days, and keep the message thread alive if there is. I've thought about this one for a while, and I've yet to come up with a way to do it...
Any help or ideas would be appreciated.
[This message has been edited by FerretComp (edited October 25, 2000).]
October 25th, 2000, 12:44 PM
make a table like this:
ThreadID <--- 1st messageid
then use a query like this:
SELECT ThreadID WHERE Date < (NOW()-(7*86400)) GROUP BY ThreadID
All the resulting threads can be deleted
i think this is the easest solution