db field(GROUP_CONCAT) as array

Question

MY tables..

articles fields:

id

name

cats fields:

id

name

article_cats fields:

id

article_id

cat_id

I have this sql:

SELECT
  a.id AS article_id ,
  a.name AS article_name ,
  COUNT( c.id ) AS num_categories ,
  GROUP_CONCAT( c.name ) AS categorie_names
 FROM
  articles AS a
 LEFT JOIN
  article_cats AS ac
 ON
  ( a.id = ac.article_id )
 LEFT JOIN
  cats AS c
 ON
  ( ac.cat_id = c.id )
 GROUP BY
  a.id

and it provide this table structure:

db structure

what i need is, to get categories id and name in array, so categorie_names will be an array with fields cat_id and cat_name for each category.

Is this possible with mysql ?

Pradeeshnarayan · Accepted Answer · 2012-08-25 22:42:35Z

4

In database array means tables. If you want data in array format the best way to store the values in a seperate temporary table.

Like

article_id, category_id, category_name
1           1            Cat1
2           1            Cat1
3           2            Cat2
3           1            Cat1

Or you have to use a concatenation inside the group_concat

GROUP_CONCAT(cast(concat(c.id,\': \',c.name) AS char)SEPARATOR \', \') AS categorie_names

So the result will be like 2:Cat2,1:Cat2. You can split(first with ',' then ':') this value and retrieve ID and Name.

answered Aug 25, 2012 at 22:42

Pradeeshnarayan

1,23510 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

db field(GROUP_CONCAT) as array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related