[Help] How to get Date from DB?

Discussion in 'PHP' started by cyimking, Aug 12, 2013.

0
  1. #1
    Hello,

    How can i get the date from the DB in this format:
    1) Date in the DB is 12am
    2) The time now is 2am...
    3) On the script, it shows the time subtracted. So it will say 2 hours ago. or if it's 2: 10am it will say 2 hours, 10 minutes ago. Or if it have been 3 days it will say the day that it was posted, the time, and the date name (Monday - Sunday)
    4) If it's under 48 hours than it will just display the hr. So 23 hours ago and 59 minutes,,,


    What is the easiest way for me to do this? Do i have to make a function?!!? Or can i just use a inner MySQL function?

    *I'm new to MySQL
     
    cyimking, Aug 12, 2013 IP
  2. samyak

    samyak Active Member

    Messages:
    280
    Likes Received:
    7
    Best Answers:
    4
    Trophy Points:
    90
    #2
    Since you have different rule for the different time duration, it would be much easier to create a PHP function that will get the date from the database, calculate time passed and output correct text based in it.
     
    samyak, Aug 13, 2013 IP
  3. cyimking

    cyimking Member

    Messages:
    14
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    31
    #3
    So... Yes the function time() - DB time? Then run a series of ifs statements?
     
    cyimking, Aug 13, 2013 IP
  4. samyak

    samyak Active Member

    Messages:
    280
    Likes Received:
    7
    Best Answers:
    4
    Trophy Points:
    90
    #4
    Wel yes. But it depends on whether the you get the date and time or time stamp from the DB
     
    samyak, Aug 13, 2013 IP
  5. cochisetm

    cochisetm Member

    Messages:
    80
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    33
    #5
    Hopefully this will get you started (assuming you are using MySQL and a datetime column). I did not quite understand all of your scenarios, but here goes (substitute "COLNAME" with your DB column name)...

    In Your MySQL select:
    SELECT
    TIMESTAMPDIFF(YEAR, COLNAME, NOW()) as year_difference,
    TIMESTAMPDIFF(MONTH, COLNAME, NOW()) as month_difference,
    TIMESTAMPDIFF(DAY, COLNAME, NOW()) as day_difference,
    TIMESTAMPDIFF(HOUR, COLNAME, NOW()) as hour_difference,
    TIMESTAMPDIFF(MINUTE, COLNAME, NOW()) as minute_difference,
    TIMESTAMPDIFF(SECOND, COLNAME, NOW()) as second_difference
    FROM BLA..........

    Get the results of the MySQL output row(s). I will assume you put them in a var called "$res":

    if($res['year_difference'] == 0 && $res['month_difference'] == 0 && $res['day_difference'] == 0 && $res['hour_difference'] == 0 && $res['minute_difference'] == 0) {
    $time_difference = $res['second_difference']. ' second';
    }
    elseif($res['year_difference'] == 0 && $res['month_difference'] == 0 && $res['day_difference'] == 0 && $res['hour_difference'] == 0) {
    $time_difference = $res['minute_difference']. ' minute';
    }
    elseif($res['year_difference'] == 0 && $res['month_difference'] == 0 && $res['day_difference'] == 0) {
    $time_difference = $res['hour_difference']. ' hour';
    }
    elseif($res['year_difference'] == 0 && $res['month_difference'] == 0) {
    $time_difference = $res['day_difference']. ' day';
    }
    elseif($res['year_difference'] == 0) {
    $time_difference = $res['month_difference']. ' month';
    }
    else {
    $time_difference = $res['year_difference']. ' year';
    }
    $time_difference .= ($time_difference == 1 ? '' : 's');

    $time_difference will now contain a formatted string showing the time difference. You should be able to tailor the code above to suit your needs, and to use a combination of if/else statements along with php's date() function to get what you are looking for.
     
    cochisetm, Aug 15, 2013 IP