mysql 暴库 group_concat(),数据库字段（GROUP_CONCAT）作为数组

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:

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 ?

解决方案

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.