ORDER BY (istrue)

[Petawa]

This is my current MySQL query:
$query = "SELECT p.post, p.topic_id, p.pid, t.tid, t.title, t.posts, t.start_date, t.starter_name, t.forum_id, t.topic_hasattach, t.rating, t.total_votes, a.attach_id, a.attach_is_image, a.attach_hits, a.attach_pid, a2.attach_thumb_location, a2.attach_pid, r.member, r.file
FROM gf_posts p
LEFT JOIN gf_topics t ON ( p.topic_id=t.tid )
LEFT JOIN gf_attachments a ON ( p.pid=a.attach_pid )
LEFT JOIN gf_attachments a2 ON (a.attach_pid=a2.attach_pid )
LEFT JOIN gf_rated r ON ( t.tid=r.file AND $memnum=r.member )
WHERE t.forum_id=80 AND t.topic_hasattach=2 AND a.attach_is_image=0 AND a2.attach_thumb_location!=''
ORDER BY ".$orderby.", a.attach_hits desc
LIMIT $page, $limit";

Which works perfectly fine. However, I was wondering if I could have it so rows whose t.start_date>$yesterday would always display before those rows whose start_dates are earlier? I don't want to order by start_date, I just want those rows who were made in the last day always go to the top of the list, with the rows who don't fit that criteria being sorted by their rating.

I read into it a little, and it appears I can do this by GROUP BY. I tried GROUP BY(t.startdate>$yesterday) but it didn't work, so obviously I don't quite grasp the usage yet.

I also did this earlier by using two separate queries. However, this fudged up my scripting when I tried to spread the results over multiple pages.

Thanks,
Peter

[raf]

I don't quite see how that could be done with group by ...

You can do it using an if() function an than sorting on the output of that. Like

$query = "SELECT p.post, p.topic_id, p.pid, t.tid, t.title, t.posts, t.start_date, t.starter_name, t.forum_id, t.topic_hasattach, t.rating, t.total_votes, a.attach_id, a.attach_is_image, a.attach_hits, a.attach_pid, a2.attach_thumb_location, a2.attach_pid, r.member, r.file, if(t.startdate>$yesterday, 1, 0) as todaysposts FROM gf_posts p
LEFT JOIN gf_topics t ON ( p.topic_id=t.tid )
LEFT JOIN gf_attachments a ON ( p.pid=a.attach_pid )
LEFT JOIN gf_attachments a2 ON (a.attach_pid=a2.attach_pid )
LEFT JOIN gf_rated r ON ( t.tid=r.file AND $memnum=r.member )
WHERE t.forum_id=80 AND t.topic_hasattach=2 AND a.attach_is_image=0 AND a2.attach_thumb_location!=''
ORDER BY todaysposts desc, ".$orderby.", a.attach_hits desc
LIMIT $page, $limit";

[Petawa]

Thanks, that worked well.

From what I read about GROUP BY, I was thinking I could do the same thing you did, except GROUP BY todaysposts.

But like I said, I didn't completely get the tutorial I was reading.

Thanks again,
Peter

P.S. - The page that this renders to is:
http://www.wbcelite.com/forums/?pg=maps