Codeigniter: How to Do a Select (Distinct Fieldname) MySQL Query

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

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

how to use DISTINCT here in my query CODEIGNITER

Try Below:

$this->db->distinct();

but Distinct will not always work. You should add ->group_by("name_of_the_column_which_needs_to_be unique");

$this->db->group_by('column_name');

codeigniter select distinct function not working

In Codeigniter, distinct does not work the way you expect it by field name. If you look at the manual - there is no argument for distinct. If you look at the code, it only takes a boolean, which defaults to true. It just adds the DISTINCT keyword to the query after the SELECT keyword. That's it.

In your case, I think it would be better to use a GROUP BY as in
$this->db->group_by('opinions.author_id');

Hopefully the order by would work as per your need in this instance by ordering before the grouping.

Cheers!

EDIT - update after OP comments

I know the ordering can be messed up - I sort of mentioned it :)
Anyway, I might be assuming some of your table structure here, but this would force the GROUP BY to pick the rows on the top. I assume that the date is on the opinions table and you only want the latest row from that with author details.

SELECT * FROM `authors`
JOIN (
    SELECT * FROM opinions
    ORDER BY `date` DESC
) AS ops ON ops.author_id = authors.author_id
GROUP BY ops.author_id

You will not be able to construct this query on active record though. Hope this helps.

How do I do this query on codeigniter framework?

If you are sure that your query is right then this is the codeigniter way to do it

$this->db->select('m.conversation_id, count(m.message_id) as message_count');
$this->db->from('cms_conversations__messages as m');
$this->db->join('cms_conversations__participants as p', 'p.conversation_id = m.conversation_id and (p.last_read IS NULL OR m.added > p.last_read) and m.user_id != 2', 'left');
$this->db->where('p.user_id', '2');
$this->db->group_by('p.user_id');
return $this->db->get()->result(); //  or you can also store it in a variable

For more you can see the documention.
Hope It helps

Convert select count() MySQL query to Codeigniter query

Hope this will help you :

You can use count_all_results to count data with where conditions

Simply run below query:

$this->db->where('status','Runing');
return $this->db->count_all_results('job_progress');

In model add the below method to count all records

public function get_count()
{
   $this->db->where('status','Runing');
   return $this->db->count_all_results('job_progress');
}

In controller :

make sure you have loaded your model containing this method

$count = $this->model_name->get_count();
echo $count;

for more : https://www.codeigniter.com/user_guide/database/query_builder.html#limiting-or-counting-results

Group By with CodeIgniter

use order_by clause as well

$this->db->group_by('nom_dept'); 
$this->db->order_by('nom_dept', 'asc');  # or desc

---

FYI : Setting SQL mode and Session set is not fix the actual error.

Examples (Better not to Do)

1. https://stackoverflow.com/a/35729681/4595675

How to write query in codeigniter for group_concat and concat with replace for table column

Your query string is not valid because this can be recognized as a string:

"SELECT GROUP_CONCAT( DISTINCT CONCAT("

and the rest does not make sense:

'", REPLACE(user_id, ",", "','") , "'")) as listed_id FROM user_data"

You should escape quotation marks when they are within quotation marks of the same kind:

$this->db->query("SELECT GROUP_CONCAT( DISTINCT CONCAT(\"'\", REPLACE(user_id,
\",\", \"','\") , \"'\")) as listed_id FROM user_data");

---

Related Topics