CodeIgniter query builder for where_not_in() with a subquery [duplicate]

Question

I am trying to code this MySQL script in my CodeIgniter model, but not getting the correct script. How to code it into query builder?

SELECT bus_reg_number
FROM t_bus_detail
WHERE bus_reg_number NOT IN (
    SELECT bus_reg_number
    FROM t_bus_alot
)

MY MODEL

public function get_bus_reg_number()
{
    $this->db
        ->select('bus_reg_number')
        ->from('t_bus_detail');
    $result = $this->db->where_not_in(
        'bus_reg_number' NOT IN (SELECT 'bus_reg_number' FROM 't_bus_alot')'
    );
    
    $dropdown_bus_number = array();
    foreach ($result as $r) {
        $dropdown_bus_number[$r['bus_reg_number']] = $r['bus_reg_number'];
    }
    return $dropdown_bus_number;
}

public function get_bus_reg_number() { $this->db->select('bus_reg_number')->from('t_bus_detail'); $result=$this->db->where_not_in('bus_reg_number' NOT IN (SELECT 'bus_reg_number' FROM 't_bus_alot')'); $dropdown_bus_number = array(); foreach($result as $r) { $dropdown_bus_number[$r['bus_reg_number']] = $r['bus_reg_number']; } return $dropdown_bus_number; } — user2180069
– user2180069, Commented Apr 7, 2013 at 16:13

Svetoslav · Accepted Answer · 2013-04-07 16:27:30Z

1

$this->db->select('bus_reg_number');
$this->db->from('t_bus_detail');
$this->db->where('`bus_reg_number` NOT IN (SELECT `bus_reg_number` FROM `t_bus_alot`)', NULL, FALSE);

Try this.. the null and false will say to CI not to escape your where query..

Edit : here is plain query:

$query = $this->db->query("SELECT bus_reg_number FROM t_bus_detail WHERE bus_reg_number NOT IN (SELECT bus_reg_number FROM t_bus_alot)");
$dropdown_bus_number = array();
foreach ($query->result_array() as $row){
   $dropdown_bus_number[$row['bus_reg_number']] = $row['bus_reg_number'];
}

edited Apr 7, 2013 at 16:27

answered Apr 7, 2013 at 16:16

Svetoslav

4,6862 gold badges30 silver badges43 bronze badges

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

2 Comments

user2180069 Over a year ago

i am finding error Severity: Notice Message: Uninitialized string offset: 0 Filename: models/transport_bus_system.php Line Number: 28 I AM POSTING MY WHOLE FUNCTION public function get_bus_reg_number() { $this->db->select('bus_reg_number')->from('t_bus_detail'); $result=$this->db->where('bus_reg_number NOT IN (SELECT bus_reg_number FROM t_bus_alot)', NULL, FALSE); $dropdown_bus_number = array(); foreach($result as $r) { $dropdown_bus_number[$r['bus_reg_number']] = $r['bus_reg_number']; } return $dropdown_bus_number; }

user2180069 Over a year ago

Severity: Notice Message: Uninitialized string offset: 0 I AM POSTING MY WHOLE FUNCTION public function get_bus_reg_number() { $this->db->select('bus_reg_number')->from('t_bus_detail'); $result=$this->db->where('bus_reg_number NOT IN (SELECT bus_reg_number FROM t_bus_alot)', NULL, FALSE); $dropdown_bus_number = array(); foreach($result as $r) { $dropdown_bus_number[$r['bus_reg_number']] = $r['bus_reg_number']; } return $dropdown_bus_number; } Filename: models/transport_bus_system.php Line Number: 28

Collectives™ on Stack Overflow

CodeIgniter query builder for where_not_in() with a subquery [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related