01-26-2012, 10:44 AM
hi guys, just a quick bit of help needed with this query, if i want to order by a specific enum in my database, then by last post on it but keeping the enum (type) with important first, then sticky, then go on to listing normal topics, but listing the normal topics in order of when the last reply was added, how would I go about doing that? below is a picture of my table structure so you guys can see what I mean:
01-26-2012, 12:11 PM
$q = "select * from TableName order by type ASC, lastreply DESC;";
01-26-2012, 12:18 PM
thats not showing anything now :S
01-26-2012, 12:33 PM
can you paste here your query that was working but just didn't order the results correctly please?
01-26-2012, 12:37 PM
SELECT * FROM `topics` WHERE `forum`='$forum' ORDER BY `lastreply` AND `sticky`!='0' DESC LIMIT $forum_look, $forum_count
hat was how it was before, then i changed the structure to the new one in the image above
01-26-2012, 12:42 PM
hmmm... that's a bit weird looking...
is this a MySQL database?
01-26-2012, 12:43 PM
yes it is, however there is pagination in there aswell as the $forum part being a GET option
01-26-2012, 12:46 PM
can you explain the sticky != 0 bit?
is that supposed to be part of the WHERE clause?
WHERE `forum`='$forum' AND `sticky`!='0'
this below is how i would have thought the query should be structured
SELECT * FROM `topics` WHERE `forum`='$forum' AND `sticky`!='0' ORDER BY `lastreply` ASC, `sticky` DESC LIMIT $forum_look, $forum_count
01-26-2012, 12:50 PM
yes it was part of the WHERE, however that was from the old db structure before I changed it to the new one in the image above
01-26-2012, 12:55 PM
so what query do you have now that is working with your current structure?
01-26-2012, 02:34 PM
You likely don't want the `sticky` in your where clause, as this will choose only entries that are (assuming?) non-sticky.
SELECT * FROM `topics` WHERE `forum` = '$forum' ORDER BY `type` ASC, `sticky`, `lastreply` DESC LIMIT $forum_look, $forum_count
No clue what your `type` ordering will be, but sticky should (likely) be descending order to indicate stick=1 is stuck, then order by lastreply descending (not ascending).