Iterate through timestamps with MySQL

Question

So, this should be a simple one. I have a JS chart that needs data in an array like so:

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
]; 

And I the data in my mysql table looks something like so:

----------------------------------------------
| 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
----------------------------------------------

What I need

The end result should print out a new element in the array per category in the table, with the SUM of the category totals in month order (for the current year).

This is as far as I've got with the SQL (yes not very) but I'm stuck from here on in.

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");

Could some PHP/SQL boffin out there just give me some direction?

Thanks all

Answer 1

Right. Spent a bit more time on this and I've got the solution:

I realise this is a specific case but just in case anyone else was looking for this then this might help.

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";
  }
?>
];