How to count the rows in a database table that have a date in the current year?

Question

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?

Answer 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%"));