Jdatabase : Counting number of elements in a comma separated field

Question

I have a Jdatabasetable having a comma separated field. What would be the best way (according to Joomla coding standards) to count the number of elements separated by commas. Like in the case below :

id comma separated values

01 (2,5,6,7,8,9,21,24,28)

It should return : 9

If I am not wrong, one of the ways to achieve it using mysql would be this :

SELECT (LENGTH(column_name) - LENGTH(REPLACE(column_name,",","")) + 1) AS MyColumnCounts
FROM table_name;

But is this the best way?

Help would be appreciated.

Answer 1

One approach would be the following:

$db = JFactory::getDbo();
$id = 20; // Example ID

$query = $db->getQuery(true);
$query->select($db->qn('roll_no'))
      ->from($db->qn('#__table_name'))
      ->where($db->qn('Id') . ' = '. $id);
$db->setQuery($query);

$result = $db->loadResult();

Then explode the result into an array, like so:

$array = explode(',', $result);

Then count the items in the array, like so:

$count = count($array);

Answer 2

Your question is not specifically about Joomla, and it isn't far from how I'd recommend doing it.

SQL Demo

SELECT SUM(CHAR_LENGTH(csv) - CHAR_LENGTH(REPLACE(csv, ',', '')) + 1) AS num_of_commas

CHAR_LENGTH() is slightly more reliable versus LENGTH(). If/When you try to process multi-byte characters with LENGTH() you will generate incorrect results.

Of course, if you wish to see the raw query translated to a Joomla select() method call, there isn't much to change:

$query = $db->getQuery(true)
            ->select("SUM(CHAR_LENGTH(" . $db->qn("column_name") . " - CHAR_LENGTH(REPLACE(" . $db->qn("column_name") . ", ',', '')) + 1) AS MyColumnCounts")
            ->from($db->qn("table_name"));

If your table_name/column_name values do not contain a space, are not MySQL reserved keywords, and are not supplied from untrustworthy sources (user-submitted), then you can safely omit the $db->qn() calls.