Order by and group by not working in php codeigniter?

Question

table format : table name category:

      id       title
   -----------------
      1          xyz
      2          abc

table name subcategory:

     id        category_id    start_date      status
 ----------------------------------------------------------
     1             1            2016-11-03       1
     2             1            2016-11-09       1
     3             2            2016-10-03       1
     4             3            2016-12-03       1
     5             3            2016-12-05       1
     6             5            2016-12-06       1

query:

      $date = date('Y-m-d');
      $this->db->select('*');
      $this->db->where('status',1);
      $this->db->where('start_date >=',$date);
      $this->db->order_by('start_date', 'asc');
      $this->db->group_by('category_id');
      $this->db->limit(4);
      $query = $this->db->get('subcategory');
      return $query->result();

my problem is: i want result as subcategory_id 1,4,6 . but group_by and order by is not working.

Error:

SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.subcategory.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT * FROM (subcategory) WHERE status = 1 AND start_date >= '2016-11-03' GROUP BY category_id ORDER BY start_date asc LIMIT 4

SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.subcategory.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT * FROM (subcategory) WHERE status = 1 AND start_date >= '2016-11-03' GROUP BY category_id ORDER BY start_date asc LIMIT 4 — K.doe
add this line at the end of your my.cnf file sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" . Then restart mysql. — Sayantan Das

Pathik Vejani Pathik Vejani · Accepted Answer · 2016-11-03T07:10:01

change your query: first there should be group by then order by

$date = date('Y-m-d');
$this->db->select('*');
$this->db->where('status',1);
$this->db->where('start_date >=',$date);
$this->db->group_by('category_id');
$this->db->order_by('start_date', 'asc');
$this->db->limit(4);
$query = $this->db->get('subcategory');
return $query->result();

Or print a query by doing this after $query and check for the same in database:

echo $this->db->last_query();

Order by and group by not working in php codeigniter?

2 Answers