CODEWITHSUNDEEP
phpmysqlcodeigniterdistinct
Ian
Ian

Reputation: 12241

CodeIgniter: How To Do a Select (Distinct Fieldname) MySQL Query

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.

Upvotes: 55

Views: 184682

Answers (5)

jonwayne
jonwayne

Reputation: 483

You can also run ->select('DISTINCT `field`', FALSE) and the second parameter tells CI not to escape the first argument.

With the second parameter as false, the output would be SELECT DISTINCT `field` instead of without the second parameter, SELECT `DISTINCT` `field`

Upvotes: 12

Sani Kamal
Sani Kamal

Reputation: 1238

Simple but usefull way:

$query = $this->db->distinct()->select('order_id')->get_where('tbl_order_details', array('seller_id' => $seller_id));
        return $query;

Upvotes: 5

joash
joash

Reputation: 2323

Since the count is the intended final value, in your query pass

$this->db->distinct();
$this->db->select('accessid');
$this->db->where('record', $record); 
$query = $this->db->get()->result_array();
return count($query);

The count the retuned value

Upvotes: 5

Mark Unwin
Mark Unwin

Reputation: 1623

$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.

Upvotes: 118

useranon
useranon

Reputation: 29514

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

Upvotes: 12

Related Questions