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 inside of CodeIgniter?

I know I can use $this->db->query(), and write my own SQL query, but I have other requirements that I want to use $this->db->where() for. If I use ->query() though 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

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();   }