SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relative Date Function

    Hey,

    In PHP, I need a function or Class with methods that allow me to pass it
    a Date string/timestamp and it returns the relative date to now.

    e.g.

    2009-03-08 returns "Yesterday"
    2009-03-01 returns "Last Week"
    2008-08-08 returns "Last Year" or something like that

    similar to the way Outlook groups email

    anyhow, its php/mysql app.. surely there is some open source code
    somewhere that will do this for me.. did a bit of a google but must be
    using the wrong terms.

    btw Mysql db so can use MySQL in query if you think that would be the
    better way to do it?

    I'm displaying a list of Sign ups and I want to group them by joining
    date.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I'm displaying a list of Sign ups and I want to group them by joining
    date.
    So what format are they stored in the database as?

    Presumably the only options are;
    yesterday
    this week
    last week
    this month
    last month
    this year
    last year
    previously

    Else tell us what those options all are.

    I guess you want to generate the correct sql snippet from an array of options like this;

    $signup = array(
    'yesterday'=> "signup_date > $today - 1 day " ,

    'last year' => "signup_date like 2008 ",
    );

    Is that what you are after.

  3. #3
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    stored in the database as Mysql Dates.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    In PHP, I need a function or Class with methods that allow me to pass it
    a Date string/timestamp and it returns the relative date to now.
    Nope, never seen one.

  5. #5
    SitePoint Member dotwebs's Avatar
    Join Date
    Mar 2004
    Location
    Ireland
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT FROM yourtable WHERE dateadded CURDATE() - INTERVAL 1 DAY 
    will return anything added in the past day. Change to hours/days/months/years as required.

    Is this what you're trying to do?
    "She's very bitter since she lost all the weight and it turns out she doesn't have a pretty face afterall!"

  6. #6
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm..

    if I have a table of members

    id, name , join_date
    1, John Doe, 2009-03-08 12:23
    2, John Doe2, 2009-01-09 12:23
    3, John Doe3, 2009-01-12 12:23
    4, John Doe4, 2009-02-09 12:23
    5, John Doe5, 2009-03-01 12:23
    6, John Doe6, 2009-03-08 08:25
    7, John Doe7, 2009-03-08 12:23
    8, John Doe8, 2009-03-09 02:15
    9, John Doe9, 2009-03-09 11:00
    10, John Doe10, 2009-03-09 12:23

    I want to list all these members on a page sorted by date of sign up where the most recent sign up is on top.

    I have this already done.. all works fine

    What i want to do now is group these on the html page by relative date
    e.g.

    --

    Today
    John Doe10 ...
    John Doe9 ...
    John Doe8 ...

    Yesterday
    John Doe7
    John Doe6

    Last Week
    John Doe5

    Last Month
    John Doe4

    2 Months ago
    John Doe3
    John Doe2

    Last Year
    John Doe1


    just like what you would see in outlook

    Now I know how to do all the code for this, its pretty straight forward but.. I thought there might be a nice function out there already made that takes in a Mysql Date OR PHP date string and returns what the relative date is.. e.g. "Today", "Yesterday", "Last Month" etc...

    surely someone has one of these functions in ther library somewhere.

  7. #7
    SitePoint Member dotwebs's Avatar
    Join Date
    Mar 2004
    Location
    Ireland
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You just need to a recordset for each:


    PHP Code:
    $sql "SELECT * from table WHERE dateadded = CURDATE()"
    $row_sql mysql_fetch_assoc($sql);

    //add heading
    echo "Today <br />:";

    //echo out your results in a repeat region
     
    do {
     echo 
    $row_sql['firstname'].$row_sql['lastname']."<br />";
      
        } while (
    $row_sql mysql_fetch_assoc($sql));

    //second one

    $sql2 "SELECT * from table WHERE dateadded = CURDATE() - INTERVAL 1DAY";
    $row_sql2 mysql_fetch_assoc($sql2);

    //add heading
     
    echo "Yesterday <br />:";

    //echo out your results in a repeat region
      
    do {
      echo 
    $row_sql2['firstname'].$row_sql2['lastname']."<br />";
      
        } while (
    $row_sql2 mysql_fetch_assoc($sql2)); 
    and so on .. using - INTERVAL WHATEVER in your select statements

    Oh! I just read the last bit of your post and see that you're looking for a function.. doh! Sorry, I don't have one but if you find one would you post it here? It could be handy!

    Thanks
    Last edited by dotwebs; Mar 10, 2009 at 12:18. Reason: not reading post properly first time
    "She's very bitter since she lost all the weight and it turns out she doesn't have a pretty face afterall!"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotwebs View Post
    You just need to a recordset for each:
    that's quite inefficient

    how about just one query, one result set, and you handle the grouping by examining the date in php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •