Implementing SQL in codeignites active record

Question

How can i convert this sql to codeigniters active record format

SELECT COUNT(*)
FROM(
SELECT DISTINCT component
FROM `multiple_sample_assay_abc`
WHERE labref = 'NDQA201303001'
)x

I have tried this function but it has sql error

 function getAssayMultipleCount($labref){
        $this->db->count_all('*');
        $this->db->from();
        $this->db->distinct('component');
        $this->db->from('multiple_sample_assay_abc');
        $this->db->where('labref',$labref);
        $query=  $this->db->get();
        return $result=$query->result();
        //print_r($result);
    }

[This](http://stackoverflow.com/questions/11023318/using-mysql-where-in-clause-in-codeigniter/11031431#11031431) might help — Muhammad Raheel, May 16 '13 at 11:50

score 0 · Answer 1 · answered May 16 '13 at 12:11

0

you are using 2 $this->db->from() statements.

answered May 16 '13 at 12:11

phirschybar

7,685
12
46
62

Bhuvan Rikka · Answer 2 · 2013-05-16T12:21:50.197

0

Try this

function getAssayMultipleCount($labref){
    $this->db->distinct('component');
    $this->db->from('multiple_sample_assay_abc');
    $this->db->where('labref',$labref);
    $query=  $this->db->get();
    $result=$query->result();
    return count($result);
    //print_r($result);
}

You can just count the no of elements after the query is executed. You don't have to use sub query for this

edited May 16 '13 at 12:21

answered May 16 '13 at 12:13

Bhuvan Rikka

2,655
1
14
27

also num_rows() could be directly used on the query object – thpl May 16 '13 at 12:31

Implementing SQL in codeignites active record

2 Answers2