How add days to date into query joomla?

Question

I need to run a query that selects records whose date is not within a date range.

More exactly, I need the records whose date is not found among their own date and 5 days later.

For example, suppose a.DateSubmitted contains this date "2016-07-10 00:00:00". I want to select the record only if a.DateSubmitted is not between 2016-07-10 00:00:00 and 2016-07-15 00:00:00.

How do I do that?

I have the following code, but It does not work. The browser returns me only this: Array (), nothing more.

$db    = JFactory::getDbo();
$query = $db->getQuery(true);

$query
    ->select('a.SubmissionId, a.DateSubmitted, b.SubmissionId, b.FieldName, b.FieldValue')
    ->from($db->quoteName('#__rsform_submissions', 'a'))
    ->join('INNER', $db->quoteName('#__rsform_submission_values', 'b') . ' ON (' . $db->quoteName('a.SubmissionId') . ' = ' . $db->quoteName('b.SubmissionId') . ')')
    ->where($db->quoteName('a.DateSubmitted') . ' NOT BETWEEN \'' . $db->quoteName('a.DateSubmitted') . '\' AND \'' . date( $db->quoteName('a.DateSubmitted') , strtotime('+5 day')) . '\'   ');

Answer 1

You are confusing PHP and MySQL in the query.

When you create a SQL query in PHP, it is a string... so $db->quoteName('a.DateSubmitted') returns the field name... it is not a date and it is not the value in your table.

In your case, you can perform the date calculation in MySQL, without MySQL-PHP conversions.

... AND DATE_ADD(DateSubmitted, INTERVAL 5 DAY)

Note: remember to adjust this string to proper PHP code.

Answer 2

You need to rethink your query logic because the following line of sql is ALWAYS going to kill ALL available rows:

->where($db->quoteName('a.DateSubmitted') . ' NOT BETWEEN \'' . $db->quoteName('a.DateSubmitted') . '\' AND \'' . date( $db->quoteName('a.DateSubmitted') , strtotime('+5 day')) . '\'   ');

In simpler terms, your logic is saying:

Only keep rows that have a DateSubmitted value that is NOT between DateSubmitted and DateSubmitted+5.

In simplest terms, if DateSubmitted equals 1, then:

Keep rows where 1 is not one of the following values: 1, 2, 3, 4, 5, 6.

---

For your logic to be corrected, you'll need two separate date sources -- one that is DateSubmitted and a separate date supply (CURDATE() or another table/column's date value).