[SOLVED] Excluding Rows Based on Timestamp and Entry

[Shadow Jolteon]

First off, I'd like to say that I have almost no experience with MySQL, so I'm sorry if this is posted in the wrong forum or if my wording is a bit off.

 

My friend and I have been running a website for a couple years now, though he's been a bit too busy lately to help out. I need help with something that's probably just really easy and I don't know how to do it. I've tried searching around a few different sites and through Google for answers, but can't really find what I'm looking for...

$topicsql = "SELECT *, MAX( messages.date ) AS maxdate, topics.id AS topicid, users.id AS userid, topics.*, users.*, groups.* FROM messages, topics, users, groups WHERE messages.topic_id = topics.id AND topics.user_id = users.id AND users.group_id = groups.id AND topics.forum_id = " . $forumid . " GROUP BY topicid ORDER BY topics.status='3' DESC, topics.status='2' DESC, maxdate DESC";

Basically, what I want to do is have it not display any entries where the topic.status is 1 after a certain amount of time has passed (probably twelve hours, based on the entry's timestamp).

 

Thank you. =)

[Ken2k7]

What's data type is date? TIMESTAMP?

[Shadow Jolteon]

Yes, date is a timestamp.

[Ken2k7]

Try this.

 

Add this in your WHERE clause:

 AND ( topics.status <> 1 OR NOW() - messages.date < (12*60*60)) 

[Shadow Jolteon]

Hm, it didn't work... =/

 

Is this the correct placement for adding it in...?

SELECT *, MAX( messages.date ) AS maxdate, topics.id AS topicid, users.id AS userid, topics.*, users.*, groups.* FROM messages, topics, users, groups WHERE messages.topic_id = topics.id AND topics.user_id = users.id AND users.group_id = groups.id AND topics.forum_id = " . $forumid . " AND ( topics.status <> 1 OR NOW() - messages.date < (12*60*60)) GROUP BY topicid ORDER BY maxdate DESC

[Ken2k7]

Wait, I think I messed up the math there. I think it should be 12*60*60*60 for 12 hours.

[Shadow Jolteon]

Awesome, that worked. Thank you very much! =)

[fenway]

You can use INTERVAL to save doing the math.