CODEWITHSUNDEEP
phpmysql
user1860996
user1860996

Reputation: 89

PHP MYSQL RETURN ARRAY IN A FIELD

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!

Upvotes: 1

Views: 50

Answers (2)

acontell
acontell

Reputation: 6932

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.

Upvotes: 1

Ashish Awasthi
Ashish Awasthi

Reputation: 1502

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

Upvotes: 1

Related Questions