Retrieving multiple emails addresses for single contact on table join

[eluser]Andy UK[/eluser]
Hello all!

What's the best practice for retrieving multiple records from a second table on a join? I've just had to normalise customer emails into a separate table so I can permit multiple emails, select primary email, etc. Beforehand there was just a single email field in the contact table. I need to return all the email addresses for a contact when querying for the contact info.

I've read about a CONCAT function for MySQL that returns multiple values in a comma separated list. I guess another option would be to create a separate query to get the emails and run through that. For a single contact that should be easy as anything, whereas for a list of contacts I would have to loop through all the contacts, query the database each time for emails and add them to an array with a key equal to the contact id.

So those are the two ways i've come across. But since this must be such a common requirement, can anyone tell me what they consider to be the best way to do it?

Cheers!

[eluser]Andy UK[/eluser]
Here's my active record query which currently only returns a single email per contact:

Code:

$this->db->select("contacts.id,
       contacts.first_name as name,
       contacts.last_name as surname,
       contact_emails.email,
       contacts.tel,
       contacts.cel,
       contacts.added_on,
       contacts.notes as client_summary,
       contacts.rating,
       client_source_types.source_label,
       status_types.status_es as status,
       status_types.color as color,
       admins.first_name as broker_first_name,
       admins.last_name as broker_last_name,
       added_by_admin.first_name as added_by_name,
       added_by_admin.last_name as added_by_surname")
    ->join('contact_emails', 'contacts.id = contact_emails.contact_id', 'left')
    ->join('status_types', 'contacts.status_id = status_types.id', 'left')
    ->join('client_source_types', 'contacts.client_source_id = client_source_types.id', 'left')
    ->join('admins', 'contacts.taken_by_id = admins.id', 'left')
    ->join('admins as added_by_admin', 'contacts.added_by = added_by_admin.id', 'left')
    ->where('contacts.id', $relationship_id);
  $query = $this->db->get('contacts');

[eluser]CroNiX[/eluser]
Well, it really depends on what you're going to do with them once you retrieve them.
Try adding a 2nd select with its 2nd parameter set to FALSE

Code:

->select('GROUP_CONCAT(`contact_emails`.`email`) AS emails', FALSE)

which will create a comma separated list of emails. Then just explode() on commas for that field to get them into an array when looping through the results.

Code:

foreach($query->result() as $row)
{
  $emails = explode(',', $row->emails);
  print_r($emails);
}

[eluser]Andy UK[/eluser]
Thanks CroNiX,

I decided to go ahead with the GROUP_CONCAT and explode method. Good idea of your to add the extra select... I had placed it in the current select with the added FALSE, but much better to separate it.