在PHP和MySql中创建累积比较表

Question

I have a database table called payments which contains date,amount fields. I want to take values from amount field and SUM up all amounts by date and take the results to html table then output them like on the image.

I have created dates dynamically so that they will be equal nomatter which months example January its 1-31 and February its 1-31. Where there is a weekend or the date is invalid i want the value to be zero. What i want is like this table [table][1] [1]: http://i.stack.imgur.com/iMOl3.jpg

This is what i am getting [output][1][1]: http://i.stack.imgur.com/MJpyT.jpg

******NOTE***** I THINK MY SOLUTION IS NOT THE BEST SOLUTION TO MY PROBLEM. IF POSSIBLE JUST TAKE I VIEW ON THE PICTURE WHICH I WANT AND FIND ME THE BEST SOLUTION. I WANT TO BE HELPED IN EITHER STEPS TO ACHIEVE IT OR A SOLUTION

I know that i am using a depricated mysql synthax please ignore that and help on my problem.

<table border="1" align="center">
  <?php
    session_start();
    include("connection/db_con.php");
    $sym='-';
    $d=array();
    ///Insert values of month for period selected into an array
    $a = $_POST['dat'];
    $b = $_POST['dat2'];
    $mnth=array();
    $m_nam=array();
    $m_nm=array();
    $m_nam[]="Day";
    //////New way of getting months in format Y-m
    $start    = new DateTime($a);
    $start->modify('first day of this month');
    $end      = new DateTime($b);
    $end->modify('first day of next month');
    $interval = DateInterval::createFromDateString('1 month');
    $period   = new DatePeriod($start, $interval, $end);

    foreach ($period as $dt) {
      $mnth[]=$dt->format("Y-m");
      $m_nam[]=date('F-Y', strtotime($dt->format("Y-m")));
      $m_nm[]=date('M', strtotime($dt->format("Y-m")));
    }
    ///////End of New way
    echo "<tr bgcolor='#999999'>";
    foreach ($m_nam as $m)
    {
      echo"<td>".$m."</td>";
    }
    echo"</tr>";
    /////////End insert////////////////////////
    $day=0;
    for($x=1; $x<=31; $x++)
    {
      $day=$day+1;
      echo"<tr>";
      echo"<td>".$day."</td>";
      $d=$sym.$x;

      foreach($mnth as $mon)
      {
        $dat=$mon.$d;

        $qry=mysql_query("SELECT SUM(amount) AS total_disb FROM payments where dat='$dat'")or die(mysql_error());
        $row=mysql_fetch_assoc($qry);
        $sum = $row['total_disb']+0;

        echo"<td>".$sum."</td>";

      }
      echo"</tr>";
    }
  ?>
</table>

Answer 1

Here's a rewrite of the code you provided, it's using dummy random data instead of DB and there is no logic for POST variables, but that you can replace with your code.

<?php   
  // session start, db connection goes here  
  echo '<table border="1" align="center">';
  // Example of post vars
  $start = new DateTime('2015-11-10');
  $end = new DateTime('2016-02-28');
  // Note: not sure why OP used modify here
  $interval = new DateInterval('P1M');
  $daterange = new DatePeriod($start, $interval, $end);

  // Table Header row 1
  echo '<tr><th>Day</th>';
  foreach ($daterange as $date) {
    echo '<th colspan="2">'.$date->format("F Y").'</th>';
  }
  echo '</tr>';

  // Temporary month store
  $months = array();

  // Table Header row 2
  echo '<tr style="background-color:#22bb22;"><th></th>';
  foreach ($daterange as $date) {
    $months[] =  $date->format("F");
    echo '<th>Daily</th>';
    echo '<th>Cumulative</th>';
  }
  echo '</tr>';

  // Table Body
  $sumc = array();
  for ($d = 1; $d <= 31; $d++) {
    echo '<tr><td>'.$d.'</td>';
    foreach ($months as $month) {
      $db_date = $month.'-'.$d; // used for db query
      // dummmy data (replace with db query result)
      $sum = mt_rand(0, 999);
      echo '<td>'.$sum.'</td>';
      if(!array_key_exists($month, $sumc)) {
        $sumc[$month] = 0;
      }
      $sumc[$month] = (int)$sum + $sumc[$month];
      echo '<td>'.$sumc[$month].'</td>';
    }
    echo '</tr>';  
  }

  echo '</table>';
?>

Also the condition:

Where there is a weekend or the date is invalid i want the value to be zero.

is it correct to assume that these are taken care because the DB query would return 0? Or do you have to check in the code for weekends even if the DB query returns an amount from total_disb that is >0?