php - MySQL和PHP查询使用标题列和基于相应标题的行输出

Question

我正在尝试做两件事，有列标题输出($accounts)，然后在相应列下添加相应的总和(z.cost)，比如水费。以及分组，因为每个属性有许多记录。它应该看起来像:

Property                     Council Rates              Water Rates
6 Hudson Road Rosemeadow          $1000                     $150
121 New York Street New York City $500

目前，它单独输出每个属性行，并且全部仅在第一列，即理事会费率中。

代码如下:

<table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-example">
    <thead>
    <tr>
        <th class='text-center'>Property</th>
        <?php
        $query_prop = "SELECT a.street_number,
                         a.street_name,
                         a.suburb,
                         z.services, 
                         z.cost, 
                         j.service_type 
                         FROM pms_property a 
                         LEFT JOIN pms_holding_costs z 
                          ON z.pms_fk_id = a.pms_id 
                         LEFT JOIN pms_accounts j 
                          ON j.service_type = z.services 
                         WHERE z.services IS NOT NULL";

        $accounts = "SELECT service_type FROM pms_accounts";
        $value = $newdb->mysqlquery($query_prop);
        $acc = $newdb->mysqlquery($accounts);

        while ($acc1 = mysqli_fetch_array($acc)) {
            echo "<th class='text-center'>$acc1[0]</th>";
        }
        ?>
    </tr>
    </thead>
    <tbody>

    <?php
    while ($row = mysqli_fetch_array($value)) {
        echo "<tr class='odd gradeX'><td>$row[0] $row[1] $row[2]</td> ";
        if ($row['service_type'] == $acc1['service_type']) {
            echo "<td>$$row[4]</td>";
        } else {
            echo "<td>$0</td></tr>";
        }
    } ?>
    <tr>
        <td colspan="1" class="text-right"><strong>Total Costs</strong></td>
        <td colspan="1"><strong>$<?php echo $row1[1]; ?></strong></td>
    </tr>
    </tbody>
</table>

<table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-example">
                            <thead>
                                <tr>
                                    <th class='text-center'>Property</th>


                                        <th class='text-center'>Council Rates</th>



                                        <th class='text-center'>Water Rates</th>


                                    </tr>
                                    </thead>
                                    <tbody>


                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>121 New York Street New York City</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>

                                    <tr class='odd gradeX'>
                                        <td>6 Hudson Road Rosemeadow</td> 

                                                <td>$0</td>
                                                </tr>
                                                                                        <tr>
                                        <td colspan="1" class="text-right"><strong>Total Costs</strong></td>
                                        <td colspan="1"><strong>$1650</strong></td>
                                    </tr>
                                </tbody>
                            </table>

Answer 1

您的问题来自数据查询，查询本身没有任何问题，只是数据未按照您预期或需要的方式格式化。

问题

SELECT
    a.street_number,
    a.street_name,
    a.suburb,
    z.services, 
    z.cost, 
    j.service_type 
FROM
    pms_property a 
LEFT JOIN
    pms_holding_costs z ON z.pms_fk_id = a.pms_id 
LEFT JOIN
    pms_accounts j ON j.service_type = z.services 
WHERE
    z.services IS NOT NULL

在这里你基本上有一个多对多的关系，许多属性可以有许多帐户。

现在在您的 Html 中，您使用此构建标题

"SELECT service_type FROM pms_accounts"

这给了你这个数组(基本上)

['Council Rates','Water Rates']

所有服务类型，这也与其他查询“不相关”。这只是意味着两者之间没有直接关系，毕竟它们是单独的查询。

您期望 z.cost 中的 2 个值或 $row[4] ( 0 based indexing )为您的每种服务类型提供一个。但是，如果您查看查询(上面)，您会发现该字段仅出现一次。因此，没有合乎逻辑的方法可以仅在一行中获得 2 个值。

别担心，我们可以用 PHP 解决这个问题。有一些方法可以使用 SQL 来解决这个问题，但如果您添加任何服务类型，它们就会成为问题(我们将在后面详细介绍)。

这个 DBfiddle 应该有助于说明这个问题。这不是您的确切架构，我删除了“表面”列，因为只有表之间的关系才是重要的。

pms_id  |       services    |   service_type
    1   |   Council Rates   |   Council Rates  //property (id 1) with only one service
    2   |   Council Rates   |   Council Rates  //property (id 2) with 2 services, 1 of 2
    2   |   Water Rates     |   Water Rates    //property (id 2) with 2 services, 2 of 2

这是你想要的结构，

pms_id  |   Council Rates   |   Water Rates | ...
    1   |    {cost}         |   {cost}   
    2   |    {cost}         |   {cost}    

解决方案

首先，我们需要属性的主 ID，所以我们唯一的标识是。所以将其添加到数据查询中，

SELECT
    a.pms_id, #<--------- add the ID
    a.street_number,
    a.street_name,
    a.suburb,
    z.services, 
    z.cost, 
    j.service_type 
FROM
    pms_property a

然后，我们将不得不卡在 $accounts 上。 ，我会调用他们 $headers因为他们进入了 table的头部.它们是此查询的结果

"SELECT service_type FROM pms_accounts"

好的，那么你在哪里循环使用 while 的结果集环形。我们只需要创建一个数组并将数据保存在其中。像这样:

$headers = [];
while ($acc1 = mysqli_fetch_array($acc)) {
     $headers[] = $acc1[0];
     echo "<th class='text-center'>$acc1[0]</th>";
}

对于这些示例和测试，我必须使用一些反射(reflect)您的数据的“ jar 头”数据，因为我不能真正使用在线数据库来做到这一点。因此，这是您的数据在 2 个查询中的基本结构，现在 {as it stand}。

$headers = ['Council Rates','Water Rates'];

$rows = [
    ['pms_id' => 1, 'street_name' => 'ONE', 'service_type' => 'Council Rates', 'cost' => 100],
    ['pms_id' => 2, 'street_name' => 'TWO', 'service_type' => 'Council Rates', 'cost' => 100],
    ['pms_id' => 2, 'street_name' => 'TWO', 'service_type' => 'Water Rates', 'cost' => 200],
];

我们必须将其更改为对手头任务更“有用”和更有意义的东西。

//We will need the result from the first query, the one for the headers. 
$headers = ['Council Rates','Water Rates']; 

/*
   first: create a "template" array by using $headers as the keys and
   fill them with 0 for the value {cost = 0, when missing}
   ----------------------------------------------------
   example. ['Council Rates'=>'0','Water Rates'=>'0']
   ----------------------------------------------------
*/
$default = array_fill_keys($headers,'0');  

$data = [];
while ($row = mysqli_fetch_assoc($value)) {
   //instead of mysqli_fetch_array, see http://php.net/manual/en/mysqli-result.fetch-assoc.php
   $service_type = $row['service_type'];

   /*
      Create a key based on the properties primary id
      ----------------------------------------------------
      I like to put a string "r" ( or anything ) in front
      of it so it doesn't get mistaken for a meaningless
      numeric index, because it's critical that we have it.     
   */
   $key = 'r'.$row['pms_id']; //such as 'r345'

    if( !isset($data[$key])){
        //if not set then it's the first pass ( Setup ) for this property

        //Create a new item with the service name as the key, and cost as the value
        $row[$service_type] = $row['cost'];

        //remove the type and cost they will just get in the way now.
        unset($row['service_type'], $row['cost']);

        /*
           by merging $row "header template" we made earlier {$default}
           ------------------------------------------------
           ['Council Rates'=>'0','Water Rates'=>'0']
           ------------------------------------------------
           we insure that any {$header} values, have a corresponding
           key in {$row}. This way there are no missing index issues,
           and we won't have to check if they have that service because
           any that don't will be conveniently filled with a '0'
           -----------------------------------------------
           example: ['pms_id' => 1, 'Council Rates'=>'100','Water Rates'=>'0']
           ----------------------------------------------------
        */
        $row =  array_merge( $default, $row);

        /*
            lastly add to our data array with our primary ID based key
            this loop iteration, is complete
        */
        $data[$key] = $row;

    }else{
        /*
             Additional passes, we only need the cost and service type.
             Because the properties {pms_id} is the primary key, and we
             have that in our sql as well as in $data from the previous
             loop iteration, we can easily find and fill in the next 
             service type. 

             -note- were not creating the service type, because we did 
              that with the {$default} array, we are simply replacing
              the '0' cost placeholder from that operation. 
             Any properties that are missing a service type will just 
             leave that 0 there.
        */
        $data[$key][$service_type] = $row['cost'];
    }
} 

//print_r($data);

这输出:

$data = Array(
    [r1] => Array(
        [Council Rates] => 100
        [Water Rates] => 0
        [pms_id] => 1
        [street_name] => ONE
    )
[r2] => Array(
        [Council Rates] => 100
        [Water Rates] => 200
        [pms_id] => 2
        [street_name] => TWO
    )
)

你可以在这里测试这部分:

http://sandbox.onlinephpfunctions.com/code/aa938ec823f8f116432eadad1f6db991e4be37dd

如您所见，我们现在拥有服务类型所需的键，并且这些数据的结构方式可以让我们轻松创建所需的 HTML 输出。这将使输出部分几乎是微不足道的。

因此，在您输出数据的地方(之前使用一个巧妙的循环，然后从数据库中读取)，我们将使用 foreach而是循环。

这两个操作必须分开进行。数据的顺序没有保证，所以我们不能一次处理几行，在同一个循环中输出。这没什么大不了的，因为我们通过组合行来减小数据数组的大小。

    //$headers = ['Council Rates','Water Rates']; //for readability

    foreach($data as $row) {
        echo "<tr class='odd gradeX'>";

        echo "<td>{$row['street_number']} {$row['street_name']} {$row['suburb']}</td>";

        /*
           Dynamically loop over headers.  We could just type this in
           but if you wanted to add more service types latter you would 
           have to come back here and add them in.  This way, you wont
           have to touch it in that case.

           This also enforces the order, we need the costs for each 
           service_type to be output in the same exact order the
           service_type headers were added into the table ( <th> ). 
         */
        foreach($headers as $header ){

            /*
              We have each service_type as a key in {$row}
              so we just pull it out with the {$header}.
            */              
            echo "<td>{$row[$header]}</td>";
        }
        echo "</tr>";
    }

输出
-注意-我不见了 street_number和 suburb在我的示例数据中，但这无关紧要(在真实的东西中)。

<tr class='odd gradeX'>
    <td> ONE </td>
    <td>100</td> <!-- Council Rates -->
    <td>0</td> <!-- Water Rates -->
</tr>

<tr class='odd gradeX'>
    <td> TWO </td>
    <td>100</td> <!-- Council Rates -->
    <td>200</td> <!-- Water Rates -->
</tr>

你可以在这里测试:

http://sandbox.onlinephpfunctions.com/code/6192b8fc732a88f212daed91423b180fd78ec4b9

在 PHP 中执行此操作会更好，因为如果我们使用查询执行此操作，它将是静态的，并且如果您添加了另一种服务类型，我们将不得不编辑查询和输出。这样，在 PHP 中，如果您添加更多服务，您应该不必进行虚拟代码更改。这是我的经验，随着时间的推移，事情的发展需要改变，你不想每次发生时都重新访问这段代码。作为开发人员，我们应该始终着眼于 future ，并使代码尽可能面向 future 和可维护。

这么多“简短”的答案，这很有趣。正如我在评论中提到的，请花时间查看上面代码中的评论，因为学习和理解比获得一段工作代码更重要。
这是完整的代码:

<table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-example">
    <thead>
        <tr>
            <th class="text-center">Property</th>
        <?php 
            //for debugging
            error_reporting(-1);
            ini_set('display_errors', -1);

            $accounts = "SELECT service_type FROM pms_accounts";
            $acc = $newdb->mysqlquery($accounts);
            $headers = [];
        ?>        
        <?php while ($acc1 = mysqli_fetch_array($acc)){ ?>
            <?php $headers[] = $acc1[0]; ?>
            <th class="text-center"><?php echo $acc1[0]; ?></th>
        <?php } ?>
        </tr>
    </thead>
    <tbody>
        <?php 
          $default = array_fill_keys($headers,'0');

          $query_prop = "
                SELECT
                    a.pms_id,
                    a.street_number,
                    a.street_name,
                    a.suburb,
                    z.services,
                    z.cost,
                    j.service_type
                 FROM
                    pms_property a
                 LEFT JOIN
                    pms_holding_costs z ON z.pms_fk_id = a.pms_id
                 LEFT JOIN
                    pms_accounts j ON j.service_type = z.services
                 WHERE
                     z.services IS NOT NULL
             ";

          $value = $newdb->mysqlquery($query_prop);

          $data = [];
          while ($row = mysqli_fetch_assoc($value)){
              $service_type = $row['service_type'];

              //row unique id
              $key = 'r'.$row['pms_id']; 

              if( !isset($data[$key])){
                  //if not set then it's the first pass ( Setup ) for this property

                  //Create a new item with the service name as the key, and cost as the value
                  $row[$service_type] = $row['cost'];

                  //remove the type and cost they will just get in the way now.
                  unset($row['service_type'], $row['cost']);

                  $row =  array_merge( $default, $row);
                  $data[$key] = $row;
              }else{
                  //Additional passes set just the service type cost
                  $data[$key][$service_type] = $row['cost'];
              }
          } 
        ?>
        <?php foreach($data as $row){ ?>
            <tr class="odd gradeX">
                <td>
                    <?php echo "{$row['street_number']} {$row['street_name']} {$row['suburb']}"; ?>
                </td> 
            <?php foreach($headers as $header){ ?>             
                <td><?php echo $row[$header]; ?></td>
            <?php } ?>
            </tr>
        <?php } ?>  
            <tr>
                <td colspan="1" class="text-right"><strong>Total Costs</strong></td>
                <td colspan="1"><strong>$<?php echo $row1[1]; ?></strong></td>
            </tr>
        </tbody>
    </table>