how i can use multiple select queries in codeigniter?

Question

how i can convert this query in codeigniter

select COUNT(*) 
from Retailers
where ID not In (select RetailerID from RetailerGroups)

i tried this

$this->db->where_not_in('ID',$this->db->query('select RetailerID from 
 RetailerGroups'));

 $query = $this->db->get('Retailers');

but it prints

Error Number: 42000

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.

SELECT * FROM Retailers WHERE ID NOT IN ()

Filename: D:\Published\faber\core\database\DB_driver.php

Line Number: 330

please help

More Detail: Message: Object of class CI_DB_sqlsrv_result could not be converted to string — user2447440
– user2447440, Commented Aug 29, 2013 at 12:54

Art K. · Accepted Answer · 2013-08-29 13:34:36Z

1

I believe you can try use that syntax:

$this->db->select('count(*)')->from('Retailers');
$this->db->where('ID not in (select RetailerID from RetailerGroups)', NULL, FALSE);

The ,NULL,FALSE in the where() tells CodeIgniter not to escape the query.

Or you can just use JOIN with this query instead of subquery.

answered Aug 29, 2013 at 13:34

Art K.

263 bronze badges

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

Comments

Nil'z · Accepted Answer · 2013-08-29 13:45:37Z

0

Try this once,

$idRs  = $this->db->select('RetailerID')->get('RetailerGroups')->result_array();
if( isset( $idRs ) && count( $idRs ) > 0 ){
    foreach( $idRs as $each ){
        $ids[]  = $each['RetailerID'];
    }
    echo "total :".$countRs    = $this->db->from('Retailers')->where_not_in('ID', $ids)->count_all_results();
}

answered Aug 29, 2013 at 13:45

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

how i can use multiple select queries in codeigniter?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related