Codeigniter - How to get values as array to use in next select with where_not_in

Question

I have three tables; user, car and user_x_car. user_x_car holds users who own car; user_id and car_id are stored. I want to get users who don't own a car as follows:

$car_owner = $this->db->select()->from('user_x_car')->get()->result();

for ($i = 0; $i < count($car_owners); $i++)
    $car_owner_id[$i] = $car_owner[$i]->user_id;

$non_car_owner = $this->db->select()->from('user')->where_not_in('id', $car_owner_id)->get()->result();

I get what I want, however, is there any way to bypass the for loop in the middle which creates and array of id's selected in the first select. Is there any way to get array of selected user_ids directly?

check this line $$car_owner_id[$i] = $car_owner[$i]->user_id; extra $ — plain jane
– plain jane, Commented Sep 20, 2013 at 5:21

ABorty · Accepted Answer · 2013-09-20 07:16:12Z

1

you can do it by two queries like

first one get all ids from user_x_car table

$temp1=array();
$temp=$this->db->distinct()->select('user_id')->get('user_x_car')->result_array();

then from user table fetch those users who have no cars

foreach($temp as $each)
{
    array_push($temp1,$each['user_id']);
}
$rs=$this->db->where_not_in('id',$temp1)->get('user');
if($rs->num_rows()>0)
{
    $data=$rs->result_array();
    print_r($data);die;
}

$data will print all users who have no car. Please let me know if you face any problem.

answered Sep 20, 2013 at 7:16

ABorty

2,5121 gold badge15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Imran Qamer · Accepted Answer · 2014-10-27 07:27:15Z

function get_unread_notifications_ids()
    {
        //get unread notifications ids
        $this->db->select('GROUP_CONCAT(fknotification_id) as alll');
        $this->db->from("te_notification_status_tbl");
        $this->db->where('read_status',0);

        $ids=$this->db->get()->row();
        return $idss=str_replace(",","','",$ids->alll);
    }

and second function like this:

function get_unviewed_photos_events(){
        $idss = $this->get_unread_notifications_ids();

        $this->db->select('img.*',False);
        $this->db->from("te_notifications_tbl notif");
        $this->db->join('te_images_tbl img','img.id=notif.reference_id','LEFT OUTER');

        $this->db->where("notif.id IN('".$idss."')");
        $rslt = $this->db->get()->result_array();
        return $rslt;
    }

John · Accepted Answer · 2013-09-20 06:03:54Z

0

Query

$non_car_owner = $this->db->query('SELECT user.*
FROM user LEFT JOIN user_x_car ON user_x_car.id=user.id
WHERE table2.id IS NULL')->result();

Here users who are not on the table user_x_car

foreach($non_car_owner as $user){
   echo $user->user_id;
}

answered Sep 20, 2013 at 6:03

John

5844 silver badges19 bronze badges

Collectives™ on Stack Overflow

Codeigniter - How to get values as array to use in next select with where_not_in

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related