return group_concat data as array

In MySQL, the GROUP_CONCAT() function allows you to concatenate values from multiple rows into a single string. However, MySQL does not have a built-in data type for arrays. Therefore, you need to convert the concatenated string into an array in your programming language.

Here are a few ways to return GROUP_CONCAT data as an array:

1. Using PHP's explode() function:

$holds = explode(',', $holds);

This will convert the concatenated string into an array of strings.

2. Using MySQL's JSON_ARRAYAGG() function (MySQL 5.7.22 and above):

SELECT JSON_ARRAYAGG(category.name) AS categories
FROM categories

This will return a JSON array of the concatenated values.

3. Using a custom MySQL function:

CREATE FUNCTION group_concat_array(str VARCHAR(255)) RETURNS JSON
BEGIN
  DECLARE result JSON;
  SET result = JSON_ARRAY();
  
  IF str IS NOT NULL THEN
    SET result = JSON_ARRAY_APPEND(result, JSON_QUOTE(str));
  END IF;
  
  RETURN result;
END

You can then use this function in your query as follows:

SELECT group_concat_array(category.name) AS categories
FROM categories

This will return a JSON array of the concatenated values.

4. Using a third-party library:

There are a number of third-party libraries that can be used to convert GROUP_CONCAT data into an array. For example, the following code uses the Doctrine\DBAL\Query\QueryBuilder library to convert the GROUP_CONCAT data into an array:

$qb = $em->createQueryBuilder();
$qb->select('category.name')
    ->from('categories')
    ->groupBy('category.id')
    ->having('COUNT(category.name) > 1');

$results = $qb->getQuery()->getArrayResult();

The $results variable will contain an array of arrays, where each inner array contains the values of the concatenated columns for a particular row.