retrieving data from two tables and display as part of one array

[eluser]ozy123[/eluser]
I have 2 tables:
users: id, name, email
user_experience: id_exp, user_id, exp_date

There are multiple users and each user has multiple experiences.

E.g.

users:

1, david, david@david.com

is linked to three experiences

user_experience:

1, 1, 1/1/2012
2, 1, 2,2,2012
3, 1, 3,3,2012

How can I retrieve the data so I can display it in a view like so:

Name / email / Experience

david, david@david.com 1/1/2012

2/2/2012

3/3/2012

joe, joe@Joe.com 1/1/2012

2/2/2012

3/3/2012
My code currently:

Controller
----------

Code:

$data['nurses'] = $this->users_model->get_nurse();

Model
----------

Code:

public function get_nurse() {
    $this->db->select('name,email,date');
    $this->db->from('users');
    $this->db->join('user_experience', 'users.id = user_experience.user_id');
    $query = $this->db->get();

    return $query->result_array();
}

View
----------

Code:

<?php foreach ($rows as $row): ?>
echo $row['first_name'];
echo $row['email'];
echo $row['exp_date'];

This however displays a new line for each user and exp combo. I want the user displayed only once. Thanks!

[eluser]vitoco[/eluser]
GROUP BY it's what you're looking for, and for show all related items to one row, use GROUP_CONCAT on the select

Slds