Q:

Getting column values as array, Zend_Db, Mysql, php

I have the following database tables:

table: person Columns: id, first_name, age, city, state
table: study Columns: id, name, description, notes
table: person_studies Columns: person_id, study_id, notes

I need to get all the study names that a particular person is involved in:  
person.id, person.first_name, person.city, study.name

This is the query I wrote to get the data for a person.id = 14:
select person.id, person.first_name, study.name from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

Since there could be multiple studies a person could be involved in, I am getting more than one row in the result set. I need to implement this using Zend_Db api's.
The questions are:
 1. Is there any better way to write this query? 
 2. If I want to get the study.name values in a separate array as part of result set, is it possible to do so in the query such that when I run the query in Zend:

    `$result = $this->fetchAll($select);` 

where $select is the sql statement
The $result should be of the following format:

    [0] => Array
    (
      [person.id] => 14
      [first_name] =>Jamie
      [study_names] => Array 
                       (
                          [0] => biotechnology;
                          [1] => mathematics;
                          [2] => aeronautics;
                       )

    )

Any help would be greatly appreciated.
Thanks

A:

I could not find a better way to write the query, but as far as the second question goes, I found the following solution of using group_concat().
The final query looks like this now:
select person.id, person.first_name, person.city, person.state, group_concat(study.name separator '|') as studynames from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

We can also specify other clauses within group_concat eg distinct . Please refer to link http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat for detailed description about this clause.

