Get records within a range of dates in Joomla

Question

Get records within a range of dates in Joomla

Navigation

#1 by (0 votes)

0

I need to run a query in Joomla (3.5.1) that selects all the records whose date is not between its own date and 5 days later.

For example, suppose that a.DateSubmitted is equal to "2016-07-10 00:00:00". I want to select that record only if a.DateSubmitted is not between 2016-07-10 00:00:00 and 2016-07-15 00:00:00 . . p>

How do I do that?

I have the following, but it does not work. The browser returns 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')) . '\'   ');

php joomla3

asked by George Berkeley 21.07.2016 в 06:19

source

1 answer

Problem with cocoapods OCI PDO does not execute the query in PHP

score 0 · Accepted Answer

It is not very clear to me where you get a.DateSubmitted , regardless of where you get the idea is the same, you are using the quoteName method that is for field names, if you want to add quotes to the dates you should use the method quote , and you're doing the between with text fields, not with actual dates:

// aunque la fecha asumo que la obtienes de otro lugar
$dateSubmitted = JFactory::getDate('2016-07-10 00:00:00');

// ... modificar el where de la query 
->where($db->quoteName('a.DateSubmitted') . ' NOT BETWEEN ' . $db->quote($dateSubmitted) . ' AND ' . $db->quote($dateSubmitted->add(new DateInterval('P5D')));