Query with INTERVAL won't work

Question

I have the following query:

  $query->select('date')    
         ->from('#__db_reports')
         ->where($db->quote('date < NOW() - INTERVAL 30 DAY'))
         $db->setQuery($query);
         $db->loadAssocList();

The problem is that the query won't work. I have already tried to execute the query directly in the MySQL Console and it's worked but not in Joomla. All other queries work but not that one with INTERVAL.

It doesn't give me any error, it just doesn't do what it's supposed to do.

Answer 1

The expression should not be quoted.

->where('date < NOW() - INTERVAL 30 DAY')

Answer 2

I see that Sharky has presented the best/correct solution, but I'd like to take a moment to build a bit of understanding around this occurrence.

For the record, date is a MYSQL Keyword, but not a Reserved Keyword, so no quoteName() calls are necessary.

First, what is the query that your script is generating...

SELECT date
FROM lmnop_db_reports
WHERE 'date < NOW() - INTERVAL 30 DAY'

Because you are calling quote() on the entire WHERE clause expression, it is being converted into a single literal string. In other words, there is no comparison logic taking place.

This is very easy to overlook. To help you with troublesome MySQL issues in the future, I recommend employing a couple of simple diagnostic techniques to help you identify potential problems.

try {
    $query = $db->getQuery(true)
        ->select('date')    
        ->from('#__db_reports')
        ->where($db->quote('date < NOW() - INTERVAL 30 DAY'));
    JFactory::getApplication()->enqueueMessage($query->dump(), 'notice');  // never show this to the public
    $db->setQuery($query);
    $result = $db->loadAssocList();
} catch (Exception $e) { // catch any syntax failures
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " , $e->getMessage());  // never show getMessage() to public
}
// do something with $result

The enqueueMessage() call is very handy because it displays its content very clearly near the top of your page. Good on ya, Joomla!

Of course, you've already told us that there were no generated errors, so the catch{} branch will not be executed in your case.

The query dump may have proven helpful to you or maybe you wouldn't have spotted the two characters (single quotes) fouling up the query logic.

So, why does your query produce an empty result set without failure? While this topic is a bit lengthy to explain, fortunately it is well covered here:

• Where clause without operator

And one final piece of advice... Because you are only generating a result set with a single column of data as a multidimensional array, your data structure can be simplified to a "flattened" indexed array by calling $db->loadColumn(). My recommendation is to declare your result set like this:

$dates = $db->loadColumn();

Then you can loop through $dates or use the full suite of array functions that php has to offer.