Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts

    ORDER BY and then DEC

    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:



    Cheers.

    Dan
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #2
    Regular Coder
    Join Date
    Jan 2012
    Posts
    271
    Thanks
    2
    Thanked 65 Times in 65 Posts
    PHP Code:
    $q "select * from TableName order by type ASC, lastreply DESC;"

  • #3
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    thats not showing anything now :S
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #4
    Regular Coder
    Join Date
    Jan 2012
    Posts
    271
    Thanks
    2
    Thanked 65 Times in 65 Posts
    can you paste here your query that was working but just didn't order the results correctly please?

  • #5
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    PHP Code:
    SELECT FROM `topicsWHERE `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
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #6
    Regular Coder
    Join Date
    Jan 2012
    Posts
    271
    Thanks
    2
    Thanked 65 Times in 65 Posts
    hmmm... that's a bit weird looking...

    is this a MySQL database?

  • #7
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    yes it is, however there is pagination in there aswell as the $forum part being a GET option
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #8
    Regular Coder
    Join Date
    Jan 2012
    Posts
    271
    Thanks
    2
    Thanked 65 Times in 65 Posts
    can you explain the sticky != 0 bit?

    is that supposed to be part of the WHERE clause?

    PHP Code:
    WHERE `forum`='$forum'  AND `sticky`!='0' 
    this below is how i would have thought the query should be structured

    PHP Code:
    SELECT FROM `topicsWHERE `forum`='$forum' AND `sticky`!='0' ORDER BY `lastreplyASC, `stickyDESC LIMIT $forum_look$forum_count 

  • #9
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    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
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #10
    Regular Coder
    Join Date
    Jan 2012
    Posts
    271
    Thanks
    2
    Thanked 65 Times in 65 Posts
    so what query do you have now that is working with your current structure?

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You likely don't want the `sticky` in your where clause, as this will choose only entries that are (assuming?) non-sticky.
    Code:
    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).


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •