$this->db->select('*, MONTH(date) as mnth');
$this->db->where('school_id', $school_id);
$this->db->group_by('MONTH(date)');
$this->db->order_by('date', 'desc');
$query = $this->db->get('events');
In above piece of code, I want to get events with respect to each month but it is returning me only the first event of each month.
I have tried it with php loops, but I want it without loops.
GROUP BY will aggregate all of the column values that match the parameter into one row. You generally need to use aggregate functions like SUM in the SELECT statement with GROUP BY. If you're selecting columns that aren't being used with an aggregate function, then you'd include them in the GROUP BY. I'm not sure exactly what you're trying to achieve, but I don't think the GROUP BY is doing what you'd expect - maybe try removing it (or add more column names to it).