Codeigniter: How to do a select (distinct fieldname) MySQL query

Question

I'm trying to retrieve a count of all unique values in a field.

Example SQL:

SELECT count(distinct accessid) FROM (`accesslog`) WHERE record = '123'

How can I do this kind of query in CodeIgniter?

I know I can use $this->db->query() and write my own SQL, but I have other requirements that I want to use $this->db->where() for, and if I use ->query() I have to write the whole query myself.

Answer 1

$record = '123';

$this->db->distinct();

$this->db->select('accessid');

$this->db->where('record', $record);

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

then

$query->num_rows();

should go a long way towards it.

Answer 2

hi, try it out with the following code

function fun1() { $this->db->select('count(DISTINCT(accessid))'); $this->db->from('accesslog'); $this->db->where('record =','123'); $query=$this->db->get(); return $query->num_rows(); }