3

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.

  • Personally I would select the entire thing then use PHP to explode the values into an array, then count the array values. May not be the best approach though – Lodder Aug 21 '16 at 17:24
  • I trust you, kindly write it in code. – saibbyweb Aug 21 '16 at 18:39
3

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);
  • Please note this is a personal preference. I'm not a backend developer so there's probably a better method – Lodder Aug 21 '16 at 18:54
  • Still a far more better approach than what I thought. Much thanks. – saibbyweb Aug 21 '16 at 19:22
0

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.

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.