返回PHP中MySQL查询的值

Question

I have a table holding order lines. There are multiple order lines per order number. Against each line there is a number in the 'diff' column which indicates if the line has been supplied complete. Zero or below, the line is complete, 1 or above and the the line is incomplete.

I need to output the total complete orders and the total pending orders.

With some help I've got as for as formulating an SQL query that is close but does not output any results in my PHP script:

$result = mysql_query("SELECT ord_number, MAX( IF( diff <=0,'COMPLETED','PENDING')) FROM  $tabl GROUP BY (ord_number)");

while($row = mysql_fetch_assoc($result)){
    echo $row['COMPLETED'];
    echo $row['PENDING'];
}

If I run the query below direct in PHPMySQL I get the output listed below:

SELECT ord_number, MAX( IF( diff <=0,  'COMPLETED',  'PENDING' ) ) 
FROM tb_raw_orderfill
GROUP BY (
ord_number
)

Output:

00000P1-OR28622 COMPLETED
00000P1-OR28623 COMPLETED
00000P1-OR28624 COMPLETED
00000P1-OR28625 COMPLETED
00000P1-OR28626 PENDING
00000P1-OR28627 COMPLETED
00000P1-OR28628 COMPLETED
00000P1-OR28629 COMPLETED
00000P1-OR28630 COMPLETED
00000S1-OR02107 PENDING
00000S1-OR02108 COMPLETED
00000S1-OR02109 COMPLETED
00000S1-OR02110 COMPLETED
00000S1-OR02111 PENDING

These are correct results,it's almost as if I need to count them somehow not sure. The results should be

Pending: 3

Complete: 11

Any help greatly appreciated.

Edit 1 - Sample Data:

Product Code    Quantity Ordered    QT Allocated    Diff    Number  Date    Quantity Supplied To Date   Status
code1   10  0   0   00000P1-OR28621 14/11/2013  10  A
code2   20  0   10  00000P1-OR28621 14/11/2013  10  S
code3   20  0   0   00000P1-OR28621 14/11/2013  20  S
code1   120 0   0   00000P1-OR28621 14/11/2013  120 A
code2   2   0   0   00000P1-OR28622 14/11/2013  2   A
code2   10  0   0   00000P1-OR28623 14/11/2013  10  A
code1   10  0   0   00000P1-OR28623 14/11/2013  10  A
code3   10  0   0   00000P1-OR28623 14/11/2013  10  A

Answer 1

This is a corrected version of Jessica's answer:

SELECT
     a.Status
    ,COUNT( a.ord_number ) AS Total
FROM (
    SELECT
         ord_number
        ,IF( MAX(diff) <= 0, 'COMPLETED', 'PENDING' ) AS Status
    FROM $table
    GROUP BY ord_number
) a
GROUP BY a.Status;

This should return two rows like so:

Status    | Total
----------|--------
COMPLETED | ####
PENDING   | ####

You could then read the results like so:

$Completed = 0;
$Pending = 0;

while($row = mysql_fetch_assoc($result)) {
    switch( $row['Status'] ) {
      case "COMPLETED":
          $Completed = $row['Total'];
          break;
      case "PENDING":
          $Pending = $row['Total'];
          break;
    }
}

If you wanted a single row with with both totals on it then see Itsols's answer.