Return Values of MySQL query in PHP

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

This method (mysql_) is deprecated. Please use mysqli_ or PDO. — Strawberry
– Strawberry, Commented Dec 18, 2013 at 16:20
What is MAX ( ) supposed to accomplish? Maybe it's working a bit differently in the two versions you tried. — Phil Perry
– Phil Perry, Commented Dec 18, 2013 at 16:26
print_r($row); to see what you should be using. It's not $row['COMPLETED'] or $row['PENDING']. — AbraCadaver
– AbraCadaver, Commented Dec 18, 2013 at 16:29
Phil - both statements are the same. The MAX is looking for a result greater than zero - based on total of the diff column against the grouped order number - at least that's my understanding. — Gebraffe
– Gebraffe, Commented Dec 18, 2013 at 16:34
Thanks AbraCadaver but the print_r($row); outputs nothing either. — Gebraffe
– Gebraffe, Commented Dec 18, 2013 at 16:35

itsols · Accepted Answer · 2013-12-18 16:50:38Z

1

If you only need the summary of completed/pending orders, I don't see why you need the MAX function. I also don't see why you need to use a while loop.

Here's what I'd do...

$result = mysql_query("SELECT 
(Select count(ord_number) as completed From $tabl Where diff<=0) as completed, 
(Select count(ord_number) as pending From $tabl Where diff>=1) as pending");

$row = mysql_fetch_assoc($result);
echo $row['completed'];
echo $row['pending'];

I haven't really tried your example, but I believe this should work.

answered Dec 18, 2013 at 16:50

itsols

5,5927 gold badges57 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AeroX · Accepted Answer · 2013-12-18 17:07:27Z

0

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.

edited Dec 18, 2013 at 17:07

answered Dec 18, 2013 at 16:56

AeroX

3,4712 gold badges28 silver badges40 bronze badges

3 Comments

Gebraffe Over a year ago

There is no group by order number - won't this just give me total of complete/pending lines rather than orders? - Just tested this and it only gives totals by line and not by order.

AeroX Over a year ago

@Gebraffe Sorry only just seen the sample data you've added. I'll correct the answer.

AeroX Over a year ago

@Gebraffe I've corrected the answer, am I correct in thinking you want the Status based on the Maxiumum value of diff for each ord_number?

Jessica · Accepted Answer · 2013-12-18 16:37:25Z

0

SELECT * FROM 
(
    SELECT 
    count(ord_number) AS total,
    MAX(IF(diff <=0,  'COMPLETED',  'PENDING')) AS type
    FROM tb_raw_orderfill
) a
GROUP BY a.type

Edit: To use subquery.

Edit2: Also, the reason your PHP doesn't work is you did not assign the max() function an alias, and there are no columns called 'COMPLETED' or 'PENDING'. Once you get a working query, use print_r() to see what the column names are.

edited Dec 18, 2013 at 16:37

answered Dec 18, 2013 at 16:24

Jessica

6,99630 silver badges39 bronze badges

3 Comments

Gebraffe Over a year ago

Thank you Jessica but that does not run - it creates an invalid use of group function.

Gebraffe Over a year ago

That runs on PHPMyAdmin but gives me a total of all order lines against pending which is wrong as I need a total of orders complete or pending.

Jessica Over a year ago

Can you post some sample data?

Collectives™ on Stack Overflow

Return Values of MySQL query in PHP

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related