Process SQL JOIN results with PHP

Question

I have an issue with processing data with PHP I retrieved from MySQL. This is the table schema:

parents:
id | firstname | lastname

children:
id | firstname | lastname

parent_child_link
child_id | parent_id

I need to save the data this way, because I'd like to link multiple parents to one child. Now when I want to retrieve a child, I'd like to get all the information of the child, but also the parent id's. This obviously is a JOIN query, so I use:

SELECT *
FROM children c
JOIN parent_child_link l on l.child_id=c.id
WHERE c.id=1

Now, when this child with id=1 has 2 parents (lets say with id's 1 and 2), I get this as a result:

id | firstname | lastname | parent_id
1    test        test       1
1    test        test       2

If I process this in PHP, I would get 2 arrays, but I want this in one array, e.g.

array(
    'firstname' => 'test', 
    'lastname' => test',
    'parents' => array(1, 2)
)

How can I achieve this?

Thanks a lot!

Answer 1

you could process you query result in php. create a new array indexed on the children id and for each item in the query result, add it to the array if it's not there already. If it is, add the parent id to its "parents" item.

Answer 2

You can use GROUP_CONCAT to return a comma seperated array of the values

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat