mysql join只返回第一行，它应该是多个

Question

I am trying to retrieve information from two tables, user and meta. However, I am only getting the first row instead of all of them.

User table looks like this

ID | Display Name | Email
1  | Test         | [e-mail]

Meta table looks like this

meta_id | user_id | meta_key | meta_value
123     | 1       | address  | 123 somewhere
123     | 1       | city     | Metropolis

This is my query

$query = $this->db->from('users as u');
$query = $this->db->join('meta as m', 'u.ID = m.user_id');
$query = $this->db->where('ID', $data['ID']);
$query = $this->db->get();

return $query->row_array();

but I get everything for the user table, but only the first row of the meta table. Trying to get all the rows that match user_id = 1 in the meta table.

What am I missing in order for this to work?

Answer 1

Did you want to retrieve all result of the query or just the first row?

$query = $this->db->from('users as u');
$query = $this->db->join('meta as m', 'u.ID = m.user_id');
$query = $this->db->where('ID', $data['ID']);
$query = $this->db->get();

Getting just one result use: row_array() returns a single result row. If your query has more than one row, it returns only the first row. The result is returned as an array.

return $query->row_array();

Getting an array of results use: result_array() method returns the query result in pure array

return $query->result_array();