PHP的MySQL的日期与yyyy-mm-dd格式 [英] php mysql group by date with yyyy-mm-dd format

问题描述

我有一个名为events的mysql表，其中包含字段：id，日期和名称。
日期字段的格式为yyyy-mm-dd hh :: mm：ss edit：表示日期时间格式

我想将事件我不知道如何处理这个问题 - 有没有办法从现场只选择月份和日期？或者我应该在选择所有事件后使用PHP

我的最终目标是这样的：

  3月10日：
 event1，
 event2 
 3月11日：
 event4，
 event5 
  

我发现 MySQL select datetime，group by date only 但我不确定如何实现它：



  SELECT DATE_FORMAT（date，'％H％i'），DATE_FORMAT（date，'％M％D'），name FROM events ORDER BY date 
  

谢谢！



编辑：



结束了使用此：

  $ sql =select team1，team2，DATE_FORMAT（date，'％Y-％m- ％d'）as created_day FROM games WHERE attack ='1'GROUP BY created_day; 
 $ result = mysql_query（$ sql）; 
 $ curDate =; （$ date！= $ curDate）
 while（list（$ team1，$ team2，$ date）= mysql_fetch_row（$ result））
 
 {
 echo$ date -------- \\\
; 
 $ curDate = $ date; 
} 
 
回声游戏数据：$ team1 $ team2; 
 
  




解决方案

完成这件事。但是由于大多数当前系统都是从显示中分离出逻辑，所以我只使用一次而不是（数天+ 1）选择，并准备一个数组，以便稍后重新显示。

  $ query =SELECT DATE_FORMAT（date，'％M％D'）as d，name FROM yourtable ORDER BY date; 
 $ foo = array（）; 
 $ result = mysql_query（$ query）; 
 while（$ row = mysql_fetch_assoc（$ result））{
 //一些逻辑来测试是否可以安全地添加名称
 $ foo [$ row ['d']] [] = $行[ '名称']; 
} 
  

然后，当我需要它时（通过模板或视图）

  foreach（$ foo as $ date => $ events）{
 echo $ date。 ：\\\
\t; 
 echo implode（，\\\
\t，$ events）; 
回声\\\
; 
} 
  

所以它符合您为自己设置的格式。

希望有助于

I had a mysql table called events with the fields: id, date, and name. The date field has the format yyyy-mm-dd hh::mm:ss edit: meaning it is in datetime format

I want to group the events by day, and I wasn't sure how to approach this- is there a way to select only the month and day from the field? or should i use PHP after I select all the "events"

my end goal is to have something like this:

March 10th: 
  event1, 
  event2
March 11th: 
  event4, 
  event5

I found MySQL select using datetime, group by date only but I'm not sure how to implement it:

SELECT DATE_FORMAT(date, '%H%i'), DATE_FORMAT(date, '%M %D'), name FROM events ORDER BY date

Thanks!

EDIT:

ended up using this:

$sql = "select team1, team2, DATE_FORMAT(date,'%Y-%m-%d') as created_day FROM games WHERE attack = '1' GROUP BY created_day";
    $result = mysql_query($sql);
    $curDate = "";

    while (list($team1, $team2, $date) = mysql_fetch_row($result))
    {
      if ($date != $curDate)
      {
        echo "$date --------\n";
        $curDate = $date;
      }

      echo "game data: $team1 $team2";
    }

解决方案

You should indeed use php to get this done. But since most of current system sepate logic from display, I'd use only one pass and not (NUMBER OF DAYS + 1) SELECTs, and prepare an array that I can reuse later for my display.

$query = "SELECT DATE_FORMAT(date, '%M %D') as d, name FROM yourtable ORDER BY date";
$foo=array();
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
    //some logic to test if it's safe to add the name
    $foo[$row['d']][]=$row['name'];
}

And then when i'd need it (through a template or your "view")

foreach($foo as $date => $events) {
    echo $date . ":\n\t";          
    echo implode(",\n\t", $events);
    echo "\n";
}

so it fits the format you set to yourself.

Hope that helped

这篇关于PHP的MySQL的日期与yyyy-mm-dd格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！