0

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')) . '\'   ');
  • Your question remains "open". Please make the necessary adjustments to your question so that this community can offer you proper support and show future researchers how to solve this issue. Please always endeavor to progress all of your questions to some sort of system-recognized resolution. – mickmackusa Jun 19 '18 at 2:03
4

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.

0

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).

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.