mysql - 无法在 Codeigniter mysql 中对带有日期标题的行进行排序

Question

我有一个这样的表结构:

ID       title             start         user_id
235   MORNING      2019-07-28 00:00:00    1234
238   AFTERNOON    2019-07-28 00:00:00    1234    
239   EVENING      2019-07-28 00:00:00    1234
240   NIGHT        2019-07-28 00:00:00    1234
241   MORNING      2019-07-29 00:00:00    1234
244   AFTERNOON    2019-07-29 00:00:00    1234
245   EVENING      2019-07-29 00:00:00    1234
247   NIGHT        2019-07-29 00:00:00    1234

我得到这样的结果

28-07-2019
AFTERNOON
MORNING
EVENING 
NIGHT 
29-07-2019
...
...

但我想要的是我的记录像这样排序

28-07-2019
MORNING 
AFTERNOON 
EVENING 
NIGHT
29-07-2019
...
...

到目前为止我尝试过的是:

 $results =  $this->db->select('*')->where('user_id',$user_id)->order_by('id','DESC')->group_by('start')->get('data_test')->result();
       foreach ($results as $result){

       }
       return $results;

我使用了这个查询，但它也不起作用:

->order_by(array('id',"CASE title WHEN '' THEN 0 WHEN 'MORNING' THEN 1 WHEN 'AFTERNOON' THEN 2 WHEN 'EVENING' THEN 3 WHEN 'NIGHT' THEN 4 END asc")

Answer 1

通过演示...

<?php

/*
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID SERIAL PRIMARY KEY
,title VARCHAR(12) NOT NULL
,start DATE NOT NULL
,user_id INT NOT NULL
);

INSERT INTO my_table VALUES
(235,'MORNING','2019-07-28 00:00:00',1234),
(238,'AFTERNOON','2019-07-28 00:00:00',1234),
(239,'EVENING','2019-07-28 00:00:00',1234),
(240,'NIGHT','2019-07-28 00:00:00',1234),
(241,'MORNING','2019-07-29 00:00:00',1234),
(244,'AFTERNOON','2019-07-29 00:00:00',1234),
(245,'EVENING','2019-07-29 00:00:00',1234),
(247,'NIGHT','2019-07-29 00:00:00',1234);
*/

require('path/to/connection/stateme.nts');



$query = "
SELECT id
     , title
     , start
     , user_id
  FROM my_table
 ORDER
    BY id;
";

$result = mysqli_query($conn,$query) or die(mysqli_error($conn));

$start = '';

while($row = mysqli_fetch_assoc($result)){

if($start == $row['start']){echo '';} else {echo '<br/>'.$row['start'];}
echo '<br/>'.$row['title'];
$start = $row['start'];

}

?>

输出:

2019-07-28
MORNING
AFTERNOON
EVENING
NIGHT
2019-07-29
MORNING
AFTERNOON
EVENING
NIGHT

...并且看不到 GROUP BY 子句。