Need help with mysql/php outputting data

Question

Okay, so, I have a list table with 2 columns: codes and dates.

I want to display the LATEST 25 AND tell the user how long ago they were submitted.

So, for example:

ABCDEF (1 Second Ago)
CCDEE (12 Seconds Ago)
329492 (45 Minutes Ago)

I've gotten this far:

$result = mysql_query("SELECT `code` FROM `fc` ORDER by datetime LIMIT 25") or die(mysql_error());

but, it doesn't do what I want. It does the reverse. It shows what was inputted FIRST, not LAST.

My output looks like this:

$output .= "<li><a href=\"http://www.***=" . htmlspecialchars(urlencode($fetch_array["code"])) . "\" target=\"_blank\">" . htmlspecialchars($fetch_array["code"]) . "</a></li>";

I have NO Idea how to add the (time since) part.

Help?

Thanks :)

score 2 · Answer 1 · edited May 23 '17 at 10:33

2

Try using

order by datetime desc

Then in PHP grab the current time, subtract the time returned from the query, and then take a look at this SO question about relative time to display your time in the proper units.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 05 '09 at 20:56

Brandon

68,708
30
194
223

Jason Musgrove · Accepted Answer · 2009-08-05T21:18:32.447

2

Consider ORDER BY datetime DESC to sort in the other direction.

Consider adding datetime to the SELECT list, so you can access the posting date in PHP. You can then use PHP date/time functions to calculte the difference between the current date, and the date posted, to work out how long ago the posting was posted.

Added: a bit of code to calculate the time since the posting in a friendly format.

$seconds = time() - strtotime($fetch_array["datetime"]);

if($seconds < 60)
    $interval = "$seconds seconds";
else
    if($seconds < 3600)
         $interval = floor($seconds / 60) . " minutes";
    else
        if($seconds < 86400)
             $interval = floor($seconds / 3600) . " hours";
        else
             $interval = floor($seconds / 86400) . " days";
 // You can keep on going

At the end $interval contains a textual representation of the interval

edited Aug 05 '09 at 21:18

answered Aug 05 '09 at 20:58

Jason Musgrove

3,574
19
14

@Frank: I've updated my answer to include the time calculations. I hope this helps. – Jason Musgrove Aug 05 '09 at 21:19
$result = mysql_query("SELECT `code`,`datetime` FROM `fc` ORDER by datetime desc LIMIT 25") or die(mysql_error()); $output .=""; ***YOUR CODE*** $fetch_array["code"] = htmlentities($fetch_array["code"]); $output .= "
" . htmlspecialchars($fetch_array["code"]) .""($fetch_array["datetime"]"

Aug 05 '09 at 21:48

@Frank: Unfortunately, SO's commenting doesn't preserve line breaks, and has made your code a little less readable than it should be. *If* I've read it right, your last line looks syntactically incorrect. Where you have `"($fetch_array["datetime"]"";` it should probably read something like `(" . $fetch_array["datetime"] . ")";`. Additionally, are you copying the the result of my code back into `$fetch_array["datetime"]`? – Jason Musgrove Aug 05 '09 at 22:42

score 0 · Answer 3 · answered Aug 05 '09 at 21:00

If I understand right the question the problem is that you don't specify the order of sort. If you want to obtain the latest posts you have to specify the descendant order.

$result = mysql_query("SELECT code FROM fc ORDER by datetime DESC LIMIT 25") or die(mysql_error());

score 0 · Answer 4 · answered Aug 05 '09 at 21:13

To fix the ordering:

"SELECT `code`, `datetime` FROM `fc` ORDER by datetime DESC LIMIT 25"

To get time diff, something like this should work. Note that you should refactor this into better methods, remove the "magic number" etc. (It can also be extended to be more sophisticated):

function getTimeAgo ($dateTime) {
    $timestamp = new DateTime($dateTime);
    $currentTimestamp = new DateTime();

    $diff = $currentTimestamp->getTimestamp() - $timestamp->getTimestamp();

    if($diff < 0) {
        throw new Exception (__METHOD__ . ':parameter $dateTime can not be 
                                           in the future!');
    }
    if($diff < 60) {
       return "$diff seconds ago";
    }
    if($diff < 3600) {
       return $diff/60 . " minutes ago";
    }
    if($diff < 86400) {
       return $diff/3600 . " hours ago";
    }
}

Two issuettes: 1 - If $diff >= 86400, getTimeAgo() will return nothing. You may want to add `return floor($diff / 86400) . " days ago";` to the end of the function. 2 - You'll get fractional results for the divisions (it's not integer math). I recommend `floor()`-ing the result to get a nice integer. — Jason Musgrove, Aug 05 '09 at 22:52
Like I said, it's an example, not a ready answer. I expect him to be fix those issues himself — PatrikAkerstrand, Aug 06 '09 at 08:03

score 0 · Answer 5 · answered Aug 05 '09 at 21:16

Changing datetime order: Try DESC or ASC at the end of your ORDER BY. This should do the trick:

SELECT code
FROM fc
ORDER BY datetime DESC
LIMIT 25

Time since: Write or find a PHP function that converts MySQL datetime format to 'real English'. Here's a quick basic example:

<?php

// your code goes here
$timeStr = "2009-08-01 15:43:34";
$time = Sec2Time( time() - strtotime($timeStr) );
print_r($time);

// this converts mysql datetime into english
//   borrowed from http://ckorp.net/sec2time.php
function Sec2Time($time){
  if(is_numeric($time)){
    $value = array(
      "years" => 0, "days" => 0, "hours" => 0,
      "minutes" => 0, "seconds" => 0,
    );
    if($time >= 31556926){
      $value["years"] = floor($time/31556926);
      $time = ($time%31556926);
    }
    if($time >= 86400){
      $value["days"] = floor($time/86400);
      $time = ($time%86400);
    }
    if($time >= 3600){
      $value["hours"] = floor($time/3600);
      $time = ($time%3600);
    }
    if($time >= 60){
      $value["minutes"] = floor($time/60);
      $time = ($time%60);
    }
    $value["seconds"] = floor($time);
    return (array) $value;
  }else{
    return (bool) FALSE;
  }
}

?>

And the output is:

Array ( [years] => 0 [days] => 4 [hours] => 5 [minutes] => 29 [seconds] => 38 )

Hope that helps

Need help with mysql/php outputting data

5 Answers5