2

I am writing a module that displays a count of the number of records depending on the date_time column. I am able to get the result that I am looking for when using a static date as below. However, I am unable to get the correct response from the code in the second block.

{
    $date       = JFactory::getDate();
    $cur_year   = JHtml::_('date', $date, 'Y');
    $db         = JFactory::getDbo();
    $query      = $db->getQuery(true);
    $query->select('COUNT(*)')
          ->from('#__aa_job_data')
          ->where("date_time LIKE '2017%'");

    $db->setQuery($query);
    $count = $db->loadResult();
    return $count;
}

The code below isn't working I can't work out the correct syntax to use for adding $cur_year & % while wrapping it in ''

{
    $date       = JFactory::getDate();
    $cur_year   = JHtml::_('date', $date, 'Y');
    $db         = JFactory::getDbo();
    $query      = $db->getQuery(true);
    $query->select('COUNT(*)')
          ->from('#__aa_job_data')
          ->where("date_time LIKE $cur_year%");

    $db->setQuery($query);
    $count = $db->loadResult();
    return $count;
}

What is the correct method to write the WHERE statement?

1

You don't need to pass a php variable into your query, just use MYSQL's YEAR() function.

You should use:

->where("YEAR(date_time) = YEAR(CURDATE())");

p.s. You were merely missing the single quotes around your $cur_year%. LIKE is not famous for being efficient anyhow.

Because your date source is "trusted", you don't need to go to any great lengths to keep your query safe from injection attacks (you can manually single quote wrap or call q() or quote()). In other situations, when you want to write an untrusted value after LIKE in a query, read this post that breaks down what to do until Joomla4's prepared statements are brought in.

For your case, both of these would have worked -- but I don't recommend them:

->where("date_time LIKE '$cur_year%'");

and

->where("date_time LIKE " . $db->q("$cur_year%"));
  • Thanks for the reply mick have just substituted the where with your suggestion but it's returning 0. I have got it working by these 2 edits $cur_year = JHtml::_('date', $date, 'Y'.'%'); and ->where("date_time LIKE '$cur_year'"); If there is a way to change the LIKE to = I would prefer that. – Twincarb Oct 7 '18 at 13:01
  • Sorry, I forgot to apply YEAR() to the column as well, so that the comparison is "apples to apples". – mickmackusa Oct 7 '18 at 13:06
  • That's working now, and it also makes sense to me which is a bonus. Many thanks. – Twincarb Oct 7 '18 at 13:20

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.