PHP MYSQL RETURN ARRAY IN A FIELD

Question

I'm a little confused how to start this. I have a table of attribute names and then a table of attribute field names. As such

**Table: attributes**
id
name

**Table: attributes_fields **
id
attrID
fieldname

The attrFields can have multiple records attached to a single attr. I'm trying to get the output something like so.

Name    Fields (comma separated field id's)
9       1,3,4,5

So far I have this.

SELECT * 
FROM attributes 
    LEFT JOIN attributes_fields on attributes.attributeID = attributes_fields.attributeFieldParentID
WHERE attributes.attributeID = 9

Which works, but not as I would like it. I'm looking for one row to be returned. The above returns all the fields as multiple rows.

Any help would be great.

Thanks!

Show PHP code, please. Use mysqli , please.

magic-sudo
– magic-sudo

2015-01-29 19:40:27 +00:00
Commented Jan 29, 2015 at 19:40 — magic-sudo
– magic-sudo, Commented Jan 29, 2015 at 19:40

Ashish Awasthi · Accepted Answer · 2015-01-29 20:08:10Z

1

You can use GROUP_CONCAT function to do this. This function returns a string result with the concatenated non-NULL values from a group. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

mysql> SELECT student_name,
    ->     GROUP_CONCAT(test_score)
    ->     FROM student
    ->     GROUP BY student_name;

Or:

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;

Please check it here: GROUP_CONCAT

answered Jan 29, 2015 at 20:08

Ashish Awasthi

1,5021 gold badge20 silver badges34 bronze badges

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

Comments

acontell · Accepted Answer · 2015-01-29 20:12:33Z

1

I was about to suggest GROUP_CONCAT as well. In your query:

SELECT a.id as Name, GROUP_CONCAT( f.id SEPARATOR ',') as Fields 
FROM attributes a
    LEFT JOIN attributes_fields f on a.attributeID = f.attributeFieldParentID
WHERE a.attributeID = 9

It should give you the result you're looking for.

answered Jan 29, 2015 at 20:12

acontell

6,9321 gold badge22 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL RETURN ARRAY IN A FIELD

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related