3

I wrote a simple SQL statement that counts the number of category IDs in a table. Problem is, its returning NULL.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('category_id','COUNT(*)')));
$query->from($db->quoteName('#__eb_event_categories'))
      ->group($db->quoteName('category_id'));
$db->setQuery($query);
$results = $db->loadObjectList(); 
var_dump($results);

When I remove COUNT(*) the select statement is working normally. What did I write incorrectly?

1 Answer 1

1

You need to remove quoteName from your select clause, so it will then look like this:

$query->select(array('category_id','COUNT(*)'));

Using $db->quoteName() on an array will wrap all array values in backticks when the query is rendered.

category_id is okay to wrap in backticks via $db->quoteName() because it is a column name.

COUNT(*) is a MySQL function. It loses its meaning if wrapped in backticks.

Your original query was seeking a column called COUNT(*) -- which of course isn't correct (or available).

1
  • So that's is correct : $query->select((array($db->quoteName('category_id'),'COUNT(*)')); ?? Oct 23, 2022 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.