Mysql Fetch Array Adding Array Totals?

system · October 26, 2012, 2:35pm

To any one who may be able to assist. I have created a quoting software for my company and I am in need of figuring out how to add up the values in the array once pulled from the database. I am unable to use the SUM() in Select since I am using JOIN to get the pricing from another column.

Please see my code below as well as a screenshot to see what I am trying to accomplish.

[php]
$qt2 = mysql_query("SELECT
customer_specs.type,
customer_specs.stone,
customer_specs.sf,
customer_specs.bs_ht,
customer_specs.edge_1_lf,
customer_specs.tearout_1_price,
customer_specs.tearout_1_sf,
customer_specs.cooktop_cutout_qty,
customer_specs.cooktop_cutout_price,
customer_specs.elect_outlet_qty,
customer_specs.elect_outlet_price,
customer_specs.sink_1_cutout,
customer_specs.sink_2_cutout,
stones.price AS stprice,
edging.price AS edprice,
s1.name AS s1name,
s2.name AS s2name,
s1.price AS s1price,
s2.price AS s2price

FROM customer_specs
INNER JOIN items
ON customer_specs.type=items.id
INNER JOIN stones
ON customer_specs.stone=stones.id
INNER JOIN edging
ON customer_specs.edge_1=edging.id
INNER JOIN sinks AS s1
ON customer_specs.sink_1=s1.id
INNER JOIN sinks AS s2
ON customer_specs.sink_2=s2.id

WHERE quote = ‘$qte’ AND c_id=’$cid’");
while($qts2 = mysql_fetch_array( $qt2 ))
{
$edgep = $qts2[‘edprice’] * $qts2[‘edge_1_lf’];
$stonep = $qts2[‘stprice’] * $qts2[‘sf’];
$tearp = $qts2[‘tearout_1_sf’] * $qts2[‘tearout_1_price’];
$cookp = $qts2[‘cooktop_cutout_price’] * $qts2[‘cooktop_cutout_qty’];
$elecp = $qts2[‘elect_outlet_price’] * $qts2[‘elect_outlet_qty’];
$sink1cp = $qts2[‘sink_1_cutout’];
$sink2cp = $qts2[‘sink_2_cutout’];
$sink1p = $qts2[‘s1price’];
$sink2p = $qts2[‘s2price’];

$total = $edgep + $stonep + $tearp + $cookp + $elecp + $sink1cp + $sink2cp + $sink1p + $sink2p;
$money=$total;
$formatted = number_format($money,2);
Print “

$”.$formatted."";
}
[/php]

If the $formatted value returns 5 or 6 results I need to add them all together somehow.

Thanks in Advance,

richei · October 26, 2012, 5:21pm

$formatted won’t return any results, its just an unnecessary variable. What you have should work though. Its going to keep returning results until there’s nothing left.

But this

$total = $edgep + $stonep + $tearp + $cookp + $elecp + $sink1cp + $sink2cp + $sink1p + $sink2p; $money=$total;
$formatted = number_format($money,2);
Print “

$”.$formatted."";

can be condensed to

echo number_format($edgep + $stonep + $tearp + $cookp + $elecp + $sink1cp + $sink2cp + $sink1p + $sink2p, 2);

If you’re doing more than one quote at a time, then i’d switch over a for loop.

m11 · October 26, 2012, 7:17pm

Do you mean that you want a grand total of all rows? For example:

[php]
$total_amount = 0;
while($qts2 = mysql_fetch_array($qt2)) {
$amount = $qts2[‘edprice’] * $qts2[‘edge_1_lf’];
$amount += $qts2[‘stprice’] * $qts2[‘sf’];
$amount += $qts2[‘tearout_1_sf’] * $qts2[‘tearout_1_price’];
$amount += $qts2[‘cooktop_cutout_price’] * $qts2[‘cooktop_cutout_qty’];
$amount += $qts2[‘elect_outlet_price’] * $qts2[‘elect_outlet_qty’];
$amount += $qts2[‘sink_1_cutout’];
$amount += $qts2[‘sink_2_cutout’];
$amount += $qts2[‘s1price’];
$amount += $qts2[‘s2price’];

echo "<td>$" . number_format($amount, 2) . "</td>";
$total_amount += $amount; // update grand total with current amount

}
echo $total_amount;
[/php]