Codeigniter Join query |
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
After you run the query, what does $this->db->last_query() report back as the run SQL?
Try this:
PHP Code: $this->db |
Welcome Guest, Not a member yet? Register Sign In |