Welcome Guest, Not a member yet? Register   Sign In
Query Builder Where Not Working With Join
#1

(This post was last modified: 11-08-2015, 11:21 PM by wolfgang1983.)

I am working on my profile display library

And I need to be able to have a db->where for my user_permission table.

It works fine and displays correct data with out it but would like to find out why when ever I use this line below it does not work


PHP Code:
$this->CI->db->where('up.user_permission_id''u.user_permission_id'); 


But if I comment that line out I can get data.

How could I still use that line and display data.


PHP Code:
<?php

class Profile {

    public function 
__construct() {
        
$this->CI =& get_instance();
        
$this->CI->load->library('session');
    }

    public function 
user_info() {
        
$this->CI->db->select('*, up.name as user_permission');
        
$this->CI->db->from($this->CI->db->dbprefix 'user_permission up''LEFT');
        
$this->CI->db->join($this->CI->db->dbprefix 'user u''u.user_permission_id = up.user_permission_id''LEFT');
        
// Where user_permission table
        //$this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
        // Where for user table
        
$this->CI->db->where('u.user_id', (int)$this->CI->session->userdata('user_id'));
        
        
$query $this->CI->db->get();
        return 
$query->row_array();
    }


Could you please explain why it return in no information with this $this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
But when I comment it out it works? I am confused.
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#2

(This post was last modified: 11-08-2015, 11:58 PM by pdthinh.)

(11-08-2015, 11:20 PM)wolfgang1983 Wrote: I am working on my profile display library

And I need to be able to have a db->where for my user_permission table.

It works fine and displays correct data with out it but would like to find out why when ever I use this line below it does not work


PHP Code:
$this->CI->db->where('up.user_permission_id''u.user_permission_id'); 


But if I comment that line out I can get data.

How could I still use that line and display data.


PHP Code:
<?php

class Profile {

 public function 
__construct() {
 
$this->CI =& get_instance();
 
$this->CI->load->library('session');
 }

 public function 
user_info() {
 
$this->CI->db->select('*, up.name as user_permission');
 
$this->CI->db->from($this->CI->db->dbprefix 'user_permission up''LEFT');
 
$this->CI->db->join($this->CI->db->dbprefix 'user u''u.user_permission_id = up.user_permission_id''LEFT');
 
// Where user_permission table
 //$this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
 // Where for user table
 
$this->CI->db->where('u.user_id', (int)$this->CI->session->userdata('user_id'));
 
 
$query $this->CI->db->get();
 return 
$query->row_array();
 }


Could you please explain why it return in no information with this $this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
But when I comment it out it works? I am confused.

I don't know the purpose of the where clause you want to use. I see the where clause and the join condition are the same. Maybe you have a mistake?
ps: You can use $this->CI->db->get_compiled_select() to review your query in plain text.
Reply
#3

(11-08-2015, 11:54 PM)pdthinh Wrote:
(11-08-2015, 11:20 PM)wolfgang1983 Wrote: I am working on my profile display library

And I need to be able to have a db->where for my user_permission table.

It works fine and displays correct data with out it but would like to find out why when ever I use this line below it does not work


PHP Code:
$this->CI->db->where('up.user_permission_id''u.user_permission_id'); 


But if I comment that line out I can get data.

How could I still use that line and display data.


PHP Code:
<?php

class Profile {

 public function 
__construct() {
 
$this->CI =& get_instance();
 
$this->CI->load->library('session');
 }

 public function 
user_info() {
 
$this->CI->db->select('*, up.name as user_permission');
 
$this->CI->db->from($this->CI->db->dbprefix 'user_permission up''LEFT');
 
$this->CI->db->join($this->CI->db->dbprefix 'user u''u.user_permission_id = up.user_permission_id''LEFT');
 
// Where user_permission table
 //$this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
 // Where for user table
 
$this->CI->db->where('u.user_id', (int)$this->CI->session->userdata('user_id'));
 
 
$query $this->CI->db->get();
 return 
$query->row_array();
 }


Could you please explain why it return in no information with this $this->CI->db->where('up.user_permission_id', 'u.user_permission_id');
But when I comment it out it works? I am confused.

I don't know the purpose of the where clause you want to use. I see the where clause and the join condition are the same. Maybe you have a mistake?
ps: You can use $this->CI->db->get_compiled_select() to review your query in plain text.

Thank you now I understand that the are the same so that's why I works with out it. So now I will remove that where cheers.
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB