CODEWITHSUNDEEP
phpmysql
user319940
user319940

Reputation: 3317

SUM of all rows from WHERE condition?

I'm trying to add the contents of all the rows retrieved from a query.

So far I have tried:

$totalquery = "SELECT SUM(catalogue.price) AS fCount
        FROM catalogue LEFT JOIN orders ON catalogue.plantid = orders.orderplantid
        WHERE orders.orderplantid = '$actualresult' AND orders.ordercustomerid = '$actualresult2' GROUP BY catalogue.price";
        $result3 = mysql_query($totalquery) or die ("Query failed : " . mysql_error());
        $totalresult = mysql_result($result3,0);

and then echoing out the $totalresult, but the problem is only displays the last value, rather than the sum of all of the values selected - any ideas on where I'm going wrong?

Upvotes: 2

Views: 343

Answers (5)

Ibu
Ibu

Reputation: 43810

instead of mysql_result() use mysql_fetch_array()

Upvotes: 0

Michael
Michael

Reputation: 35341

Try removing the GROUP BY statement.

Also, if $actualresult and $actualresult2 is input from the user, be sure to escape these to avoid SQL injection attacks:

$totalquery= "... WHERE orders.orderplantid = '" . mysql_real_escape_string($actualresult) . "' AND orders.ordercustomerid = '" . mysql_real_escape_string($actualresult2) . "'";

Upvotes: 0

Marc B
Marc B

Reputation: 360592

If the grouping results in multiple groups being formed, you'd need to retrieve the rows in a loop:

while($row = mysql_fetch_assoc($result3)) {
    $total = $row['fCount'];
    ... do something with total ...
}

As it stands now, you're only fetching the first summed value and throwing away the rest.

Upvotes: 1

eykanal
eykanal

Reputation: 27017

Remove GROUP BY catalogue.price from the end of the line; the sum command IS one of the aggregate functions.

Upvotes: 2

thepip3r
thepip3r

Reputation: 2935

most likely because there are multiple results instead of just one. and because you're not looping through the array and storing all values to view, all you're seeing is the last value of the result-set because it's the last assignment ot the $totalresult variable...

try mysql_fetch_assoc($result3) or mysql_fetch_array($result3) and loop through the results to get all returned information...

Upvotes: 0

Related Questions