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

php mysql codeigniter distinct
Ian picture Ian · Mar 18, 2009 · Viewed 147k times · Source

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

Mark Unwin picture Mark Unwin · Mar 18, 2009 
$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.

Related questions

CodeIgniter: Unable to connect to your database server using the provided settings Error Message

I have been using CI just fine using the MySQL driver. I want to use the MySQL driver instead, but as soon as I change it (just add the ‘i’ at the end of MySQL, and added the port number) …

Read More
insert multiple rows via a php array into mysql

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a …

Read More
Inserting NOW() into Database with CodeIgniter's Active Record

I want to insert current time in database using mySQL function NOW() in Codeigniter's active record. The following query won't work: $data = array( 'name' => $name , 'email' => $email, 'time' => NOW() ); $this->db->insert('mytable', $data); This …

Read More