[SOLVED] Mysql Math Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Mysql Math Problem

Post by bdeonline »

Ok here is what I have

A 2 mysql columns with money values with quantities:
atest / btest
5.00 / 2
5.50 /5

I want to be able to
times atest row 1 with btest row 1
times atest row 2 with btest row 2
and so on then add a total

Code: Select all

$query = "SELECT * FROM test";
  $countRows = $db_mysql->query($query);

while ($countRow = mysql_fetch_array($countRows, MYSQL_ASSOC)) {
    $total = $countRowї'atest'] * $countRowї'btest'] . " ";
    $total2 = explode(" ", $total);
}
    echo array_sum($total2);
So what I have been trying is taking the data from the db into two arrays times them and then adding the sum but after the data is retrieved it chages it into a string so I changed it back into a array but it still isn't working right.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Perhaps the following might be of interest. Not the solution perhaps, but some ideas of what can be done:

Code: Select all

$res = mysql_query("select cost, quantity, cost * quantity as total from test");
    while ($row = mysql_fetch_assoc($res)) {
        echo "<pre>
            Cost: {$row['cost']}
            Quantity: {$row['quantity']}
            Grand total: {$row['total']}
        \n";
    }
Result:

Code: Select all

Cost: 5.00
            Quantity: 10
            Grand total: 50.00
        

            Cost: 2.00
            Quantity: 255
            Grand total: 510.00
I'm letting the database itself deal with the math, instead of trying to re-invent the wheel again using php. Might just work for you also.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

<?php

$query = "SELECT (`atest` * `btest`) AS `product` FROM `test`";
$res = $db_mysql->query($query);

$result = array();
while($row = mysql_fetch_assoc($res))
  $result[] = $row['product'];
}

echo array_sum($result);

?>
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post by bdeonline »

That worked just as I needed thanks.
Post Reply