SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    May 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Show most current rows according to date

    I have a site where I post updated stories on events. I want to be able to post the most current ones according to date.

    I have it set up right now in a ifelse statement and that just seems really cover some. What I want to happen is if I posts on today (2013/05/30) then the posts from today would show in this spot. However if I didn't post today and my last post was on 2013/05/29 I would want those posts to show in that are. Same would be true if I didn't post today and my last post was a year ago. This can be anywhere from 1 post a day to 5 posts a day depending on how much has changed.

    I also want to set up another area below the most recent posts that show the next most recent post. Same concept as above except these would be a day or two old. Unless of course I didn't post for a week or two. Then it would just be second most current.

    I am pulling the data from a MySQL database and site is in PHP.

    Any help would be much appreciated.

  2. #2
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Order them with mysql when you select them from the database based on date.

    SELECT
    post_title,
    post_text

    FROM
    table

    ORDER BY
    date_field DESC

  3. #3
    SitePoint Member
    Join Date
    May 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I could order them like this, but then how do I only show the the most current ones in one area and older ones in another area.
    I have a container where I want the most recent stories to show. Then I have another container where I want the second most recent stories to show. There is also a third container that would show the third most recent stories.

  4. #4
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just use separate MySQL queries for the different sections in conjunction with MySQL "LIMIT" (http://php.about.com/od/mysqlcommands/g/Limit_sql.htm).

    To bring back the 5 most recent items ordered by date use:

    SELECT
    post_title,
    post_text

    FROM
    table

    ORDER BY
    date_field DESC

    LIMIT 5


    To bring back 5 items starting at the 10th most recent item in the database use:

    SELECT
    post_title,
    post_text

    FROM
    table

    ORDER BY
    date_field DESC

    LIMIT 9,5


    You can also select items where the date is between 2 specified dates, so you could bring back items for a specific month or day:

    SELECT
    post_title,
    post_text

    FROM
    table

    WHERE
    date_field BETWEEN '2013-01-01' AND '2013-02-01'

    ORDER BY
    date_field DESC

    LIMIT 5

  5. #5
    SitePoint Member
    Join Date
    May 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand that you can limit the number of results with the limit. Maybe I am not being clear enough. I have three different blocks that hold stories. First block holds stories from most current day, second block will hold stories from next current day, third will hold stories from third most current day. There can be anywhere from 1 story to 100 stories in a day. Do I still need to use the limit?

  6. #6
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    iN that case dont use LIMIT and just use the WHERE date_field conditions


Tags for this Thread

Bookmarks

Posting Permissions

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