• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Codeigniter Join query

#1
CODEIGNITER + MySQL DB
Hi I have two tables

Table 1

<pre><code>
CREATE TABLE `users` (
  `user_id` int(255) NOT NULL,
  `user_name` varchar(100) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_password` varchar(200) NOT NULL,
  `user_phone` varchar(20) NOT NULL,
  `building_units` int(200) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('0','1') NOT NULL DEFAULT '1' COMMENT '1 = Active(default), 0 = Inactive',
  `user_role` enum('0','1','2','3') NOT NULL DEFAULT '2' COMMENT '0=Manager,1=Admin,2=User,3=Tenant',
  `user_ip` int(39) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table 2
<pre><code>
CREATE TABLE `building_admins_tbl` (
  `id` int(255) NOT NULL,
  `building_id` int(255) NOT NULL COMMENT 'FK ',
  `building_admin_id` int(255) DEFAULT NULL COMMENT 'FK',
  `user_id` int(255) DEFAULT NULL,
  `tenant_id` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I have written the following query.
<pre><code>
$select = 'users.user_id,users.user_name,users.user_role,users.status,building_admins_tbl.building_admin_id';
$join_str = 'users.user_id != building_admins_tbl.building_admin_id';
$where = ['users.user_role'=>'1','users.status'=>'1'];
        
        $q =$this->db    
                    ->select($select)
                    ->where($where)
                    ->from('users')    
                    ->join('building_admins_tbl',$join_str)
                    ->get();
        $admin_list =$q->result_array();

In the above query, Join string is important, I want to get only those rows where(users.user_id != building_admins_tbl.building_admin_id) with where condition as well. 

But I am not getting the expecting result, Please help me whats wrong in the query. Help appreciated.

Thanks
Reply

#2
After you run the query, what does $this->db->last_query() report back as the run SQL?
Reply

#3
How is the result different than expected?
Reply

#4
Try this:

PHP Code:
$this->db
->select('u.user_id,u.user_name,u.user_role,u.status,b.building_admin_id')
->
from('users u')
->
join('building_admins_tbl b','u.id = b.building_admin_id')
->
where('u.user_role >=' '1')
->
where('u.status =>''1')
->
where('b.building_admin_id',NULL);
$q $this->db->get();
$admin_list =$q->result_array(); 
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.