CODEWITHSUNDEEP
mysqlcodeigniter
Evan
Evan

Reputation: 409

What is wrong with this Code Igniter mySQL query?

I have two tables for storing information about a user. One is for authentication, the other is information the user will enter themselves. I am writing a model that will be used when the user interacts with this information. The following method is to return data for display and modification.

I need a query that will return 'email' and 'username' from $accounts_table and * from $profiles_table. I can't seem to get my head around the JOIN syntax though. I understand how joins work, but my queries throw sentax errors.

function get_userdata($id){
     $data = array();

     $this->db->get_where($this->profiles_table, array('user_id' => $id));
     $this->db->join($this->accounts_table.'.email', $this->accounts_table.'.id = '.$this->profiles_table.'.user_id');
     $data= $this->db->get();

     return $data;
}

Upvotes: 2

Views: 178

Answers (3)

uzsolt
uzsolt

Reputation: 6027

I think you've a mistake:

 $this->db->join($this->accounts_table.'.email', $this->accounts_table.'.id = '.$this->profiles_table.'.user_id');

First parameter should a table NOT a field: $this->accounts_table.'.email' is wrong IMHO. Or only a typo :)

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227260

get_where executes the query. So, your join is its own query, which doesn't work.

You need to break get_where into where and from.

Also, in MySQL, you JOIN a table, not a field. If you want that field, add it to the SELECT.

$this->db->select($this->profiles_table.'.*');
$this->db->select($this->accounts_table.'.email,'.$this->accounts_table.'.username');
$this->db->from($this->profiles_table);
$this->db->where('user_id', $id);
$this->db->join($this->accounts_table, $this->accounts_table.'.id = '.$this->profiles_table.'.user_id');
$data = $this->db->get();

NOTE: $this->db->get() returns a query object, you need to use result or row to get the data.

Upvotes: 1

Justin Lucas
Justin Lucas

Reputation: 2321

I see a couple of issues:

You should be using $this->db->where(), instead of $this->db->get_where(). get_where() executes the query immediately.

$this->db->get_where('user_id', $id);

Also the first argument of $this->db->join() should only be the table name, excluding the field.

$this->db->join($this->accounts_table, $this->accounts_table.'.id = '.$this->profiles_table.'.user_id');

And you're returning $data which is just an empty array(). You would need to pass the query results to $data like this:

$data = $record->result_array();

Upvotes: 2

Related Questions