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.
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47
  1. #1
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts

    time in database to return certian value

    sorry if the title isnt very clear, i just didnt know how to word it, anyways, the point of this topics is, that im wondering how to pull a quesry from the database and have it display either "Today", "Yesterday", "1 Day Ago", "4 days ago" ect ect, 4 in that last one being X ammount of days, i am trying to get it from the value of this feild in the database:

    lastseen

    which produces a value in the format of date and time like so:

    2012-02-19 05:14:31

    so my question is, how exactly would i do this, as im not very good with the time functions and quesrys in php.

    Cheers if you can help, also if you need more explanations please just ask

    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
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    there is certainly a function in SQL that lets you get the difference in days. then you can either use a SQL procedure to convert 0 into today and 1 into yesterday or you do that part in PHP.

    PS. I deem "yesterday" and "1 day ago" synonymous
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Quote Originally Posted by Dormilich View Post
    PS. I deem "yesterday" and "1 day ago" synonymous
    that is true, i was using it as an example but you are completely correct.

    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

  • #4
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    can anyone help me with this please?
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #5
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    what do you need help with? the MySQL date/time functions are here.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #6
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    well what i need help on is whats in my OP, basically converting the time stamp, from what it is, to something like "Yesterday", "2 Days Ago", "3 Days Ago" so on, so forth.
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #7
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    the first step would be calculating the date difference. for that I have given you a link. once you have that it’s just a matter of if()s to make that into a human understandable string.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #8
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    on that note, is this a reasonable approach?

    PHP Code:
    $utime=strtotime($fetch2->lastseen);
    $eachday 86400;

    $days$utime-86400
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #9
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    I would do the date difference calculation in SQL. it has that many date functions for a reason.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #10
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    im sorry, i dont really understand, ive only recently started learning different methods of using sql, such as table joins, so could you possibly, if you dont mind, show me some sort of example?
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #11
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    let’s use a slightly off-topic date function to demonstrate
    Code:
    -- show all dates in european format that are not older than one week
    SELECT DATE_FORMAT(mydate, GET_FORMAT(DATE, 'EUR')) FROM mytable WHERE DATE_ADD(mydate, INTERVAL 1 WEEK) > NOW();
    PS. there is a function that does exactly what you want.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #12
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    is this right?

    PHP Code:
    SELECT TO_DAYS(lastseenFROM users WHERE username='$viewuser' 
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #13
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    does the result meet your expectation?
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #14
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    well ive tried this:

    PHP Code:
    $querytestnew mysql_query("SELECT TO_DAYS(lastseen) FROM users WHERE username='DOS1392'");
    $fetchnewnew mysql_fetch_object($querytestnew);
    echo 
    "$fetchnewnew->lastseen"
    however its not displaying anything at all, have i gone wrong somewhere?
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

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

  • #15
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,474
    Thanks
    13
    Thanked 361 Times in 357 Posts
    yes, twice.
    - there is no lastseen property in your fetched object
    - property calls do not work inside strings

    hint: look 3 functions up
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer


  •  
    Page 1 of 4 123 ... LastLast

    Posting Permissions

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