CODEWITHSUNDEEP
phpmysqlcodeigniterjoincodeigniter-3
JonYork
JonYork

Reputation: 1243

mysql join only returning the first row when it should be multiple

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         | [email protected]

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?

Upvotes: 0

Views: 409

Answers (3)

Karthik Saravanan
Karthik Saravanan

Reputation: 158

$this->db->reset_query();       
$this->db->select("*");
$this->db->from('user');
$this->db->join('meta', 'user.id = meta.id ' );
$this->db->where('user.id="'. $data['ID'].'"'); // where conditions
$query = $this->db->get();
return $query->result();

Upvotes: 0

Keith Asilom
Keith Asilom

Reputation: 179

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();

Upvotes: 1

Abdullah Al Shakib
Abdullah Al Shakib

Reputation: 2044

Try this. Hope it may be helpful

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

return $query->row_array();

Upvotes: 0

Related Questions