+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How i can select most common value with codeigniter (/showthread.php?tid=414)
How i can select most common value with codeigniter - Smith - 11-30-2014
Hi guys,
I have a question. I wanted to know how to select the most common value in a table while doing a join. For example: I have a "blog" table and another table "comments_blog" and I want to select from the "comments" the ID of the post table that most repeats.
The table would have this content:
// id - id_post - content
1 2 test
2 2 test2
3 2 test3
4 3 test4
4 5 test5
I looked in the official guide of codeigniter but found nothing. and truth i never done this type of query. I Try doing this:
Code:
$this->db->join( 'comments_blog', 'comments_blog' . '.' . 'id_blog' . ' = ' . 'blog' . '.' . 'id' );
$query = $this->db->query('SELECT id_blog AS magnitude FROM comments_blog GROUP BY id_blog ORDER BY magnitude DESC LIMIT 3');I want to do this to know which is the most popular post. Not get me wrong, I do not want to you do the work for me, i just want to explain to me how you could do
RE: How i can select most common value with codeigniter - sv3tli0 - 11-30-2014
Its more sql question than codeigniter.. group by and order by count perhaps...
RE: How i can select most common value with codeigniter - bclinton - 12-01-2014
It looks like you are trying to do something like this: http://stackoverflow.com/questions/7693613/select-most-common-value-from-a-field-in-mysql
First of all, you need to select count(*) as magnitude, not id_blog as magnitude. Look at the example in that answer more closely.
Secondly, you are mixing Active Record syntax ( $this->db->join() ) with the command for doing a simple query ( $this->db->query() ). I could be wrong, but I don't think you can mix those two things.
I think you either need to add the join to the $this->db->query() statement, or use the Active Record syntax here: http://www.codeigniter.com/user_guide/database/active_record.html $this->db->select(), $this->db->get(), etc
Make sure CodeIgniter is sending the query as you expect by using the profiler $this->output->enable_profiler(TRUE); or sending $this->db->last_query(); to the log
RE: How i can select most common value with codeigniter - Smith - 12-02-2014
Hi sv3tli0,
i know it. But did not know how to do it using Active Record syntax.
thanks bclinton, now i understand better.