php - 使用 php 和 mysql 进行交叉表

Question

我有一个这种格式的数据库表

费用金额的日期

======================================

2015年1月1日 2015年1月7日 AGTI 500
2015 年 1 月 1 日 2015 年 1 月 7 日 AGTII 700.50
2015 年 8 月 1 日 2015 年 1 月 14 日 AGTI 330.19
2015 年 8 月 1 日 2015 年 1 月 14 日 AGTII 540.19

现在我想根据用户给出的日期范围来显示它

收费  01/01/15-07/01/15        08/01/15-14/01/15          总计

================================================== ===================
AGTI 500 330.19830.19
AGTII 700.50 540.191240.69
=================================================== ==================
合计 1200.50 870.382070.88

如果用户从日期日历中选择 01/01/15-07/01/15，则仅01/01/15-07/01/15 列和总计值将会出现，如果选择 01/01/15-14/01/15，而不是列 2 周，则将显示总总值。我对这个问题很困惑，请帮忙..

Answer 1

这不是一个完整的解决方案。然而，它确实通过仅回答问题标题来为网站增加值(value)。这意味着此查询不是动态的并且不使用日期选择器。如您所见，指定的日期是硬编码的，但很容易调整。然而，这将为您提供一个相当好的起点，以达到您想要的目标。

看看这个 SQLfiddle查看数据库架构的详细信息。

<?php

$host = "localhost"; // the hostname or ip address of the mysql server
$user = "user"; // this is the username for mysql
$pass = "pass"; //password for mysql server
$db = "database"; //name of your database

$conn = mysqli_connect($host, $user, $pass, $db); // connect to the database with the values we defined above

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); // show an error if we fail to connect
  }

  $sql = "SELECT charge,
            SUM((CASE Date_from
                    WHEN '2015-01-01' 
                        THEN amount
                        ELSE NULL
                    END)) AS `week1`,
                SUM((CASE Date_from
                    WHEN '2015-01-08' 
                        THEN amount
                        ELSE NULL
                    END)) AS `week2`,
                SUM(amount) AS total
            FROM charges
            GROUP BY charge
            ORDER BY charge ASC";

  $query = mysqli_query($sql, $conn); //define our query
// leave php and define some html table headers
?>
<table>
    <tr>
        <th>Charge</th>
        <th>01/01/15-07/01/15</th>
        <th>08/01/15-14/01/15 </th>
        <th>Total</th>
    </tr>
<?php //now back to php to display our results.

//we want to add a TOTAL line at the bottom so we're going to need to add these up
//start by declaring a counter as zero for each thing we want to count
$w1total = 0; //week 1 total
$w2total = 0; // week 2 total
$gtotal = 0; // week 3 total

//loop through each row in the query
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){

    $w1total += $row['week1']; // add the values to our counters
    $w2total += $row['week2'];
    $gtotal += $row['total'];

    // build a html row for each of our results
    echo "<tr>";
        echo "<td>".$row['charge']."</td>";
        echo "<td>".$row['week1']."</td>";
        echo "<td>".$row['week2']."</td>";
        echo "<td>".$row['total']."</td>";
    echo "</tr>";
  }

  //and finally add our totals row
  echo "<tr>";
    echo "<td>Total</td>";
    echo "<td>".$w1total."</td>";
    echo "<td>".$w2total."</td>";
    echo "<td>".$gtotal."</td>";
  echo "</tr>";
 echo "</table>";

 mysqli_close($conn); //close our connection when we're finished.

 ?>