How to read row of query and calculate multiple rows in PHP MySQL?

Question

This is extension of my previous question. My previous question solved my problem. the link How to calculate rows in query PHP MySQL?

But it only work if each row of query have different values (i.e 1st row = 1 and 2nd row = 1.5). only then it calculate these two rows.

It does not work and it cannot calculate if the row have the same value (i.e 1st row = 1 and 2nd row = 1). it only shows single row

The code as below

<?php
    $sql    = "SELECT  sum_hour,
                    SUM(sum_hour)
                    FROM table
                    WHERE type_move = 'P' AND user_id='username' AND (start_date >= '2016-11-11' AND end_date <= '2016-11-15')
                    GROUP BY sum_hour ";

    $result = mysqli_query($connect, $sql);
    if($result && mysqli_num_rows($result) > 0)
    {
        $total = 0;
        while($row = mysqli_fetch_assoc($result))
        {   
            $hour   = $row['sum_hour'];
            $total += $hour;  
            echo " Hours = " .$hour. "<br/>";
            print_r($row);
        }

        echo "Total hours = " .$total. "<br/>";

    mysqli_free_result($result);
    }
    else
    {
        echo mysqli_error($connect);
    }
?>

Can someone help ?

Thanks.

yeah . but it only work if the rows of query have different value . if two rows have the same value, it does not work — Jamilah
– Jamilah, Commented Nov 29, 2016 at 9:03
is strange .. try add var_dump($total); after $total += $hour; so you can check the result step by step — ScaisEdge
– ScaisEdge, Commented Nov 29, 2016 at 9:32

ADyson · Accepted Answer · 2016-11-29 11:06:21Z

1

I think the issue is with the grouping in the SQL - it seems unnecessary. Grouping by a field and then doing the SUM of that field is a bit of a contradiction.

For example, if you have four rows like this:

| sum_hour |
------------
| 3.0      |
| 4.5      |
| 1.0      |
| 1.0      |

Then running your query as-is should produce the following rows:

----------------------------
| sum_hour | SUM(sum_hour) |
----------------------------
| 3.0      | 3.0           |
----------------------------
| 4.5      | 4.5           |
----------------------------
| 1.0      | 2.0           | <-- all the 1.0s in the table have been grouped together by the GROUP BY clause and then the SUM of those rows calculated

If you just want the total of all of them, this seems a bit pointless.

Change

SELECT  sum_hour, SUM(sum_hour)
FROM table
WHERE type_move = 'P' AND user_id='username' AND (start_date >= '2016-11-11' AND end_date <= '2016-11-15')
GROUP BY sum_hour

to

SELECT SUM(sum_hour) AS total
FROM table
WHERE type_move = 'P' 
  AND user_id='username' 
  AND (start_date >= '2016-11-11' AND end_date <= '2016-11-15')

This way you should just get one value returned from the query containing the SUM of all the matching rows in the database, and you probably don't need your PHP loop at all.

In my example above, this altered query would produce the following result:

---------
| total |
---------
| 9.5   |

Then in your PHP, you only need to retrieve one value, as follows:

$result = mysqli_query($connect, $sql);

if($result)
{
    $total = 0;

    if($row = mysqli_fetch_assoc($result))
    {   
        $total = $row['total'];
    }

    echo "Total hours = " .$total. "<br/>";
//...etc

edited Nov 29, 2016 at 11:06

answered Nov 29, 2016 at 9:10

ADyson

62.9k16 gold badges86 silver badges100 bronze badges

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

7 Comments

Jamilah Over a year ago

I see. i'll try it first . i'll let you know later . thanks :)

Jamilah Over a year ago

Your suggestion code does not work . it didnt calculate the row .

ADyson Over a year ago

@Jamilah please give more detail. what was the result of the query using my suggestion? And what was wrong with it in comparison to yours? (I'm not talking about the PHP part yet, just the SQL)

Jamilah Over a year ago

when i run it in sql , the value sum perfect and no problem at all . but shoudl be (1 + 1 = 2) . but the ouput in php still show 1 . the output does not calculate the row .

ADyson Over a year ago

@Jamilah so the query in my version returns one row, with the value 2 in it, correct?

|

ScaisEdge · Accepted Answer · 2016-11-29 09:36:42Z

0

Be careful with group by sum_hour

"SELECT  sum_hour,
           SUM(sum_hour)
            FROM table
WHERE type_move = 'P' AND user_id='username' 
AND (start_date >= '2016-11-11' AND end_date <= '2016-11-15')
   GROUP BY sum_hour ";

if you have 2 rows with sum_hour = 1 then the query above return SUM(sum_hour) = 2 because is the correct sum of a grouped by key

try with 3 or more rows with sum_hour = 1 and you see that the sum increase for the fact that 1 became a key for group by

answered Nov 29, 2016 at 9:36

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

How to read row of query and calculate multiple rows in PHP MySQL?

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related