4

I have data stored in a db table, with an auto timestamp. In a module I want to then display a result based on the latest timestamp.

It's not working and I'm getting an error

Warning: strtotime() expects parameter 1 to be string

The query I've written is:

//Get Latest Update TimeStamp
$db = JFactory::getDBO();
$query = "SELECT MAX( timestamp )  
          FROM #__service_status ORDER BY timestamp DESC LIMIT 1";
$db->setQuery( $query);
$timestamp = strtotime($db);

Then I'm trying to echo it with:

<?php echo date("m-d-Y", $timestamp);?>
3

You need to add this in your code, after $db->setQuery( $query);

$db->setQuery( $query);
$var1= $db->loadResult();
$timestamp = strtotime($var1);

Hope this help

  • 1
    @RustyJoomla - Just thought I'd mention that it was because you were pushing the whole Joomla Database object to strtotime rather than the result of the database query ;) – Lodder Sep 25 '15 at 12:59
  • 1
    If loadResult() will return null you will have a warning also. I advice to check the result before use it further, like if (!empty($var1)) do something. – Dmitry Rekun Sep 27 '15 at 6:56
1

MySQL already provides the appropriate tools to reformat your timestamp value, so I strongly recommend that you wrap DATE_FORMAT() around your MAX() call.

SELECT DATE_FORMAT(MAX(timestamp), '%m-%d-%Y') AS `formatted` FROM `service_status`

SQLFiddle Demo

MAX() provides a more succinct query versus ORDER BY timestamp DESC LIMIT 1 which will also provide the desired output.

SELECT DATE_FORMAT(timestamp, '%m-%d-%Y') AS formatted FROM `service_status` ORDER BY timestamp DESC LIMIT 1

The take-away lessions are:

  1. You don't need the redundant logic of MAX() and ORDER BY timestamp DESC LIMIT 1.
  2. You can spare a couple of php date function calls (namely: strtotime() & date()) by reformatting in your query.

The final baked product can look like this:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("DATE_FORMAT(MAX(timestamp), '%m-%d-%Y')")
                ->from("#__service_status");
    // echo $query->dump();  // uncomment if you want to see what is generated
    $db->setQuery($query);
    if (!$result = $db->loadResult()) {  // declare $result then check for a "falsey" value
        echo "No Rows Found";
    } else {
        echo "Last update was $result";
    }
} catch (Exception $e) {
    echo "Syntax Error"; // , $e->getMessage();  // <- don't show these details publicly
}

*since there is only a single value returned, I have removed the column alias that I first mentioned in my raw SQL query -- loadResult() doesn't care what the field is called.

*timestamp is a mysql KEYWORD but it is not a RESERVED KEYWORD so it doesn't need to be wrapped in backticks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.