Add values of multiple fields from one table and subtract it to a field from another table in php

Question

I have two tables : contract bill.
contract table contains following fields(contId (PK), contractNumber, quantity, etc..).
bill contains following fields(billId (PK), billNo, contId, checkMtr, etc...).

Here every contract can have multiple bills. What I am trying to do is add all the inwMtr of a contract and deduct that from the quantity and display the final mtrs as Pending Mtr.

Here is what I have tried:

<?php

include("dbconnection.php");

$sql1 = mysql_query("SELECT quantity FROM contract WHERE contId = '25'");
while($result1  = mysql_fetch_array($sql1))
{
//echo "$result1[quantity]";
//echo "<br/>";


//echo "<br/>";

$sql2 = mysql_query("SELECT checkMtr FROM bill WHERE contId = '25'");
while($result2  = mysql_fetch_array($sql2))
{
//echo "$result2[checkMtr]";
//echo "<br/>";

$a = $result1['quantity'] - $result2['checkMtr'];
echo "$a";
echo "<br/>";

}
echo "<br/>";

}
echo "<br/>";
?>

Here it does subtract checkMtr from quantity but it does it for every bill. It doesn't add checkmtrs of all the bills and then subtract.

Any suggestions?

Answer 1

$sql2 = mysql_query("SELECT sum(checkMtr) checkMtr FROM bill WHERE contId = '25'");
if (mysql_num_rows($sql2) == 1) {
  $result2  = mysql_fetch_array($sql2);
  $a = $result1['quantity'] - $result2['checkMtr'];
} else {
  $a = $result1['quantity'];
}
echo $a;

There's also no need for the while loop after the first query. It can never return more than one row, since you're selecting the row by primary key.

You can also combine this into one query:

SELECT quantity - ifnull(sum(checkMtr), 0) result
FROM contract c
LEFT JOIN bill USING (contId)
WHERE c.contId = '25'

To do this for all contracts, use GROUP BY:

SELECT c.contId, quantity - ifnull(sum(checkMtr), 0) result
FROM contract c
LEFT JOIN bill USING (contId)
GROUP BY c.contId

SQLFIDDLE