用MySQL迭代时间戳

Question

所以，这应该是一个简单的问题。我有一个JS图表，需要这样的数组中的数据：

var seriesData = [
   [13.58, 14.99, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],  //this is the totals in month order
   [0, 0, 17.32, 0, 0, 0, 0, 0, 0, 0, 0, 0],
    //lines are repeated per category in table
]; 

我的mysql表中的数据如下所示：

----------------------------------------------
| t_id |    t_date    | t_total | t_category |   //very simplified 
|   1  |  1483257600  |  13.58  |    C1      |   //there are a lot more categories
|   2  |  1485936000  |  14.99  |    C1      |   //but this gives the idea
|   3  |  1488355200  |  17.32  |    C2      |   //dates will be random
----------------------------------------------

​

我所需要的

最终结果应该在表中的每个类别的数组中打印一个新元素，类别总数的SUM按月份顺序排列(当前年份)。

这就是我对SQL的理解(是的，不是很大)，但从现在开始我就陷入困境了。

mysqli_query($mysqli,"select sum(t_total), FROM_UNIXTIME(t_date, '%Y') AS year from table 
where FROM_UNIXTIME(t_date, '%Y') = year(curdate()) group by t_category");

一些PHP/SQL能给我一些方向吗？

谢谢大家

Answer 1

正确的。在这个问题上花了更多的时间，我找到了解决办法：

我意识到这是一个特殊的案例，但万一其他人正在寻找这件事，这可能会有所帮助。

var seriesData = [
<?
$gc = mysqli_query($mysqli,"select distinct `t_category` from `table` where `t_user` = '1' and `t_status`='$tstatus' order by `t_category` asc ")or die(mysqli_error($mysqli));
  if(mysqli_num_rows($gc) > 0){
    while($gcr = mysqli_fetch_assoc($gc)){
      $categ = $gcr["t_category"];
      $start = $month = strtotime('first day of january this year');
      $end = time();
      $gsmarr = [];
        while($month < $end){
          $mnmb = new DateTime("@$month");
          $mn = $mnmb->format('n');
          $gs = mysqli_query($mysqli,"select sum(`t_total`) as `grspc` from `table` where `t_user`='1' and `t_category`='$categ' and `t_status`='$tstatus' and month(from_unixtime(`t_date`)) = '$mn' ")or die(mysqli_error($mysqli));
             while($gsr = mysqli_fetch_assoc($gs)){
               if($gsr["grspc"] == ""){
                  array_push($gsmarr,'0');
               }else{
                  array_push($gsmarr,$gsr["grspc"]);
               }
             }
          $month = strtotime("+1 month", $month);
      }
    $impmnt = implode(',',$gsmarr);
    echo "[".$impmnt."],";
   }
  }else{
     echo "oops";
  }
?>
];