Mysql -> getting results from two tables?

[eluser]Johan André[/eluser]
I would do it like this:

users
id
username
...
...


invoices
id
user_id
...
...

Using an id in the invoices table to refer users is more correct. What if a user with many invoices changes his username? Then you would have to update the username-colum in all invoices.

Get this list of invoices for a user with:

Code:

$this->db->select('*');
$this->db->from('invoices')
$this->db->join('users', 'users.id = invoices.user_id', 'inner');
$this->db->where('users.id', $user_id);
$result = $this->db->get()->result_array();

And then in view:

Code:

foreach($result AS $item) :
...
endforeach;