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.