MySQL Join result

[eluser]ibnclaudius[/eluser]
I have two tables that have fields with the same name, how can I select each one?

Code:

$data['get_subjects'] = $this->mb_database->get_subjects($this->session->userdata('school_id'));

Code:

public function get_subjects($school_id)
{
  $query = $this->db->select('*')->from($this->subjects_table, $this->classes_table)->join($this->classes_table, $this->classes_table . '.id = ' . $this->subjects_table . '.classes_id', 'INNER')->where($this->classes_table . '.id', $school_id)->get();

  if ($query->num_rows > 0)
  {
   return $query;
  }
  else
  {
   return FALSE;
  }
}

Code:

<?php foreach($get_subjects->result_array() as $row) : ?>
     <p>nome: &lt;?php echo $row['name']?&gt;</p><br />
    &lt;?php endforeach; ?&gt;

For example, when I do the foreach, it always return the name from $this->classes_table

[eluser]vbsaltydog[/eluser]
You need to use the table prefixes if you want to get the same column from two different tables.

[eluser]ibnclaudius[/eluser]
I tried this:

Code:

$query = $this->db->select($this->subjects_table . '.*', $this->classes_table . '.*')->from($this->subjects_table, $this->classes_table)->join($this->classes_table, $this->classes_table . '.id = ' . $this->subjects_table . '.class_id', 'INNER')->where($this->classes_table . '.id', $school_id)->get();

Code:

<?php foreach($get_subjects->result_array() as $row) : ?>
     <p>nome: &lt;?php echo $row['subjects.id']?&gt;</p><br /> //$this->subjects_table = subjects
    &lt;?php endforeach; ?&gt;

But got this error:

A PHP Error was encountered

Severity: Notice

Message: Undefined index: subjects.id

Filename: user/subjects.php

[eluser]CroNiX[/eluser]
results only come back as field names, not table.field. If you have 2 fields with the same name you need to cast one AS something else to make it unique.

Code:

SELECT subjects.id AS subjects_id

[eluser]ibnclaudius[/eluser]
For example, I tried this, but didnt work.

Code:

$query = $this->db->select($this->users_table . '.id AS user_id', $this->users_table . '.name AS user_name', $this->users_table . '.email AS user_email', $this->users_table . '.type AS user_type')
    ->from($this->users_table)
    ->where(array('email' => $email, 'password' => $password))
    ->get();

Code:

$user_data = $this->CI->mb_database->login_user($email, $password);
$this->CI->session->set_userdata('logged_in', TRUE);
   $this->CI->session->set_userdata('id', $user_data['user_id']);
   $this->CI->session->set_userdata('name', $user_data['user_name']);
   $this->CI->session->set_userdata('email', $user_data['user_email']);
   $this->CI->session->set_userdata('type', $user_data['user_type']);

What am I doing wrong?

[eluser]ibnclaudius[/eluser]
I did a print_r on $user_data, got this:

Code:

Array ( [user_id] => 1 )

Looks like he only selected the user_id, why?

[eluser]CroNiX[/eluser]
Look at your select. You are separating everything with commas which are separate arguments being passed to select(). You can pass different arguments, but not those. You want to be creating a single string, not separate arguments. See the user guide for db:elect(). Also looking at $this->db->last_query() should help.

[eluser]ibnclaudius[/eluser]
Sorry, but I do not understand what you said :S

In user guide there's a select like this:

Code:

$this->db->select('title, content, date');

Isnt the same?

[eluser]CroNiX[/eluser]
Your commas are outside of the string, making them separate arguments being passed to select().

[eluser]ibnclaudius[/eluser]
Got it, but still not working.

Code:

public function login_user($email, $password, $remember)
{
  $user_data = $this->CI->mb_database->login_user($email, $password);
  
  if ($user_data)
  {
   $this->CI->session->set_userdata('logged_in', TRUE);
   $this->CI->session->set_userdata('id', $user_data['user_id']);
   $this->CI->session->set_userdata('name', $user_data['user_name']);
   $this->CI->session->set_userdata('email', $user_data['user_email']);
   $this->CI->session->set_userdata('type', $user_data['user_type']);
  }
}

Code:

public function login_user($email, $password)
{
  $query = $this->db->select($this->users_table . '.id AS user_id, ' . $this->users_table . '.name AS user_name, ' . $this->users_table . '.email AS user_email, ' . $this->users_table . '.type AS user_type')
    ->from($this->users_table)
    ->where(array('email' => $email, 'password' => $password))
    ->get();
  
  if ($query->num_rows > 0)
  {
   return $query->row_array();
  }
  else
  {
   return FALSE;
  }
}