PHP中的组查询结果 [英] Group Query Results in PHP

问题描述

我使用以下查询来填充表格：

I am using the following query to populate a table:

$result = mysql_query("SELECT vendor, part_number, machine, Sum(q_received) AS received, Sum(q_rejected) AS rejected FROM qa_parts Group by vendor, part_number, machine Order by vendor ASC");

我想按供应商对结果进行分组，然后在供应商显示的所有部件来自他们和他们的详细信息。

I want to group the results by the Vendor then under the vendor display all the parts that have come from them and their details.

这就是我需要做的事情：

This is what I need it to do:

http://www.gen-techno.com/images/old.jpg

现在正在做什么：

http://www.gen-techno.com/images/new.jpg

尽可能请参阅供应商OPS产品的零件不在该供应商下分组，如上面Allied Electronics的示例中的零件。

As you can see the the parts for Vendor O.P.S Products are not being grouped under that vendor like the parts are in the above example for Allied Electronics.

我的表格代码截至目前：

My table code as of now:

while($row = mysql_fetch_array($result))
  {
echo "<h4>" . $row['vendor'] . "</h4>[fancy_table]<table>
<tr>
<th>Part Number</th>
<th>Quantity Received</th>
<th>Quantity Rejected</th>
<th>Machine</th>
</tr>";


  echo "<tr>";
  echo "<td>" . $row['part_number'] . "</td>";
  echo "<td>" . $row['received'] . "</td>";
  echo "<td>" . $row['rejected'] . "</td>";
  echo "<td>" . $row['machine'] . "</td>";
  echo "</tr>";
  echo "</table>[/fancy_table]";}

推荐答案

在每个循环中，您必须决定是否该抽出标题 - 您将列出新的供应商。如果不是新供应商，您仍然会列出您绘制最后一个标题的部分，因此您不需要标题或结束该表。

During each loop you have to decide if it is time to draw the heading--you're going to list a new vendor. If it isn't a new vendor, you're still listing parts for the one you drew the last heading for, so you don't want the heading or to end the table.

$current_vendor = false;
while($row = mysql_fetch_array($result)) {
    // listing a new vendor? Output the heading, start the table
    if ($row['vendor'] != $current_vendor) {
            if ($current_vendor !== false)
                echo '</table>[/fancy_table]'; // if we're changing vendors, close the table
        echo '
            <h4>'.$row['vendor'].'</h4>[fancy_table]
            <table>
                <tr>
                <th>Part Number</th>
                <th>Quantity Received</th>
                <th>Quantity Rejected</th>
                <th>Machine</th>
                </tr>
        ';
        $current_vendor = $row['vendor'];
    }
    // output the row of data
    echo '<tr>
        <td>'.$row['part_number'].'</td>
        <td>'.$row['received'].'</td>
        <td>'.$row['rejected'].'</td>
        <td>'.$row['machine'].'</td>
        </tr>
    ';
}
echo '</table>[/fancy_table]'; // close the final table

另一方面，您应该开始更新此代码以使用PDO。显然，您正在更新过去存在的东西......让PDO成为此更新的一部分。 mysql _ * 函数正在弃用，很快它们就会消失。

On another note, you should start updating this code to use PDO. Obviously, you're in the middle of updating something that used to exist... make PDO a part of this update. The mysql_* functions are on the way to deprecation, and soon they'll be gone.

PDO不是很难过渡到现在，当你已经处理过的事情比以后更容易，当你面对它没有破坏，不解决它的问题时。

PDO is not hard to transition into, and it is MUCH easier now while you're already working on things than it will be later, when you're faced with the "it isn't broke, don't fix it" problem.

以下是使用卓越的PDO DBA方法快速查看代码：

Here's a quick look at your code using the superior PDO DBA method:

   $pdo = new PDO("mysql:host=localhost;dbname=database", '-username-', '-password-');
   $sql = '
        SELECT
            vendor, 
            part_number, 
            machine, 
            SUM(q_received) AS "received",
            SUM(q_rejected) AS "rejected" 
        FROM 
            qa_parts 
        GROUP BY 
            vendor, 
            part_number, 
            machine 
        ORDER BY 
            vendor ASC
    ';
    $statement = $pdo->prepare($sql);
    $statement->execute();
    while ($order = $statement->fetch()) {
        /* Put your table code here! */
    }

文档

• foreach - http://php.net/manual/en/control-structures.foreach.php


• mysql_query （ DEPRECATED ） - http://php.net/manual/en/function.mysql-query.php


• PDO - http://www.php.net/manual/en/book.pdo.php


• PDO :: prepare - http://www.php.net/manual/en/pdo.prepare.php


• PDOStatement :: fetch - http://www.php.net/manual/en/pdostatement .fetch.php


• PDOStatement :: execute - < a href =http://www.php.net/manual/en/pdostatement.execute.php =nofollow> http://www.php.net/manual/en/pdostatement.execute.php

• foreach - http://php.net/manual/en/control-structures.foreach.php
• mysql_query (DEPRECATED) - http://php.net/manual/en/function.mysql-query.php
• PDO - http://www.php.net/manual/en/book.pdo.php
• PDO::prepare - http://www.php.net/manual/en/pdo.prepare.php
• PDOStatement::fetch - http://www.php.net/manual/en/pdostatement.fetch.php
• PDOStatement::execute - http://www.php.net/manual/en/pdostatement.execute.php

这篇关于PHP中的组查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！