CodeIgniter Forums
MySQL Join result - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Join result (/showthread.php?tid=48789)

Pages: 1 2

MySQL Join result - El Forum - 01-28-2012

[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

MySQL Join result - El Forum - 01-29-2012

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

MySQL Join result - El Forum - 01-29-2012

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

But got this error:
Quote:A PHP Error was encountered

Severity: Notice

Message: Undefined index: subjects.id

Filename: user/subjects.php


MySQL Join result - El Forum - 01-29-2012

[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


MySQL Join result - El Forum - 01-29-2012

[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?

MySQL Join result - El Forum - 01-29-2012

[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? Confused

MySQL Join result - El Forum - 01-29-2012

[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:Confusedelect(). Also looking at $this->db->last_query() should help.

MySQL Join result - El Forum - 01-29-2012

[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?

MySQL Join result - El Forum - 01-29-2012

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

MySQL Join result - El Forum - 01-29-2012

[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;
  }
}