CODEWITHSUNDEEP
phphtml-table
Lawrence
Lawrence

Reputation: 41

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

What it is doing now:

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

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

Upvotes: 2

Views: 3364

Answers (2)

Chris Baker
Chris Baker

Reputation: 50622

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

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

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! */
    }

Documentation

Upvotes: 3

Marc B
Marc B

Reputation: 360922

You need to split your loop into two parts. First part checks if you've got a new/different vendor, and sets up the "outer" setup. Then the inner part which dumps out that vendor's parts.

e.g.

$prev_vendor = null;
while($row = ...) {
    if ($row['vendor'] != $prev_vendor) {
       ... new vendor stuff ...
       $prev_vendor = $row['vendor']; // update vendor for next iteration
    }
    ... dump this row's machine data ...
}

Upvotes: 0

Related Questions