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 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [solved] Intermingling most current "Posted" and "Updated" MySQL query results

    I'm trying to figure out how to display articles stored in a MySQL database in the order they were either last updated or originally posted, whichever is later, and display the 5 most current results on the main page. Currently, these are two separate variables, so both "Posted" and "Updated" dates can be displayed on each article's page.

    Example of what I want:

    Posted: Sept 12
    Updated: Sept 9
    Posted: Sept 8
    Posted: Sept 6
    Updated: Sept 3

    Could this be done in the query itself, or would I need two separate queries (SELECT...ORDER BY posted DESC LIMIT 0, 5) and (SELECT...ORDER BY updated DESC LIMIT 0, 5), then choose the 5 most current from those 10 results and integrate them with code from there?

    "SELECT ... ORDER BY updated DESC, date DESC LIMIT 0, 5";
    ...obviously doesn't work, since it will pull updated before posted, regardless of whether something was posted after something else was updated or not.

    Thanks in advance.
    Last edited by webby; 09-13-2005 at 08:28 PM. Reason: [solved]

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    why don't you add a timestamp (a column of type timestamp) and then just order on that? When the record is created + each time that the record is changed, the timestamp is automatically updated so if you just order on that ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah, so basically when a new article is added, a timestamp can be applied for $posted (as is already done) as well as a new variable like $listing_order (or whatever), and every time an article is updated, it would insert the same timestamp for both $updated and $listing_order, and then I could simply ORDER BY 'listing_order'.

    I was so concerned with getting the variables out that were already in there that I hadn't even thought of adding a new one, lol.

    Thanks, raf!
    Last edited by webby; 09-13-2005 at 08:27 PM. Reason: clarification


  •  

    Posting Permissions

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