[SOLVED] Easiest way to multiply results of two colums

[travelkind]

I have created this script that fetches my data to a web page.  This is fine except I need to multiply two of the colums together and have that result displayed instead: for example multiply 16.11 * 50 to equal 8.06.  I also will have a "0" in this field for some of them.

 

Here is my current report data:

35,JFM01,10/18/2009,16.11,50,Clear,601500898

35,LS01,10/19/2009,27.87,100,Clear,601500884

35,HS,10/18/2009,71.19,0,Clear,601500446

 

What I need to get to display:

35,JFM01,10/18/2009,8.06,Clear,601500898

35,LS01,10/19/2009,27.87,Clear,601500884

35,HS,10/18/2009,0,Clear,601500446

 

Here is my script:

<?php

 

$dbhost = "xxxxx";

$dbuser = "xxxxx";

$dbpassword = "xxxxx";

 

$dbdatabase = "ccfee";

 

$db = mysql_connect($dbhost, $dbuser, $dbpassword);

mysql_select_db($dbdatabase, $db);

 

// Make a MySQL Connection

// Construct our join query

$query = "SELECT sunocoimport.duns_num, custmast.gl_num, custmast.division_num, custmast.customer_num, sunocoimport.summary_date, sunocoimport.cc_fees, custmast.fee_percent, custmast.clear FROM sunocoimport, custmast WHERE sunocoimport.duns_num = custmast.duns_num";

 

$result = mysql_query($query)or die(mysql_error());

 

// Print out the contents of each row into a table

while($row = mysql_fetch_array($result)){

          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".$row['cc_fees'].",".$row['fee_percent'].",".$row['clear'].",".$row['gl_num'];

          echo "<br />";

}

?>

 

Thanks for any help you can give!

Dave

 

 

[smerny]

while($row = mysql_fetch_array($result)){
          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",". $row['cc_fees'] * $row['fee_percent'] /100 .",".$row['clear'].",".$row['gl_num'];
          echo "<br />";

?

[nadeemshafi9]

$query = "SELECT sunocoimport.duns_num, custmast.gl_num, custmast.division_num, custmast.customer_num, sunocoimport.summary_date, sunocoimport.cc_fees, custmast.fee_percent, custmast.clear, SUM(col_1, col_2) AS sumed_collum

 

 

 

FROM sunocoimport, custmast WHERE sunocoimport.duns_num = custmast.duns_num";

[nadeemshafi9]

while($row = mysql_fetch_array($result)){
          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",". $row['cc_fees'] * $row['fee_percent'] /100 .",".$row['clear'].",".$row['gl_num'];
          echo "<br />";

?

 

yes you can do that if the database is too large but is not the proper way of doing it, but its used alot to counter a data summing time lapse in large tables

[smerny]

for something this simple i don't see a reason to alter the query... but anyway, i'm not sure how you are attempting to get the percent * fees with a SUM of col1 and col2? SUM is addition, and what are you adding?

 

if you were going to do this via sql query, you would still have to multiply fees by percent and 0.01

[nadeemshafi9]

for something this simple i don't see a reason to alter the query... but anyway, i'm not sure how you are attempting to get the percent * fees with a SUM of col1 and col2? SUM is addition, and what are you adding?

 

if you were going to do this via sql query, you would still have to multiply fees by percent and 0.01

 

me personaly i would do it in sql first for my own benefit and then change it if they say its slow, you can do a percentage in sql just need to use the right combination of functions

 

however if you have a model for your databses setup giving full results then yes your way would be right, i was just doing it for his sake it would be neater and more understandable and less code.

 

But yeh for that it would be good to do it your way, quick and simple

[travelkind]

Thanks a lot guys!  One more thing, how do I control the decimal so that reads 66.56 instead of 66.555?  I guess you force the decimal to the 2nd place.

[nadeemshafi9]

http://www.google.co.uk/#hl=en&source=hp&q=php+decimal+place&btnG=Google+Search&meta=&aq=f&oq=php+decimal+place&fp=c662ce1e81ac315b

[smerny]

Thanks a lot guys!  One more thing, how do I control the decimal so that reads 66.56 instead of 66.555?  I guess you force the decimal to the 2nd place.

 

 

while($row = mysql_fetch_array($result)){
          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",". round($row['cc_fees'] * $row['fee_percent'] / 100 , 2) .",".$row['clear'].",".$row['gl_num'];
          echo "<br />";

[travelkind]

Thanks Smerny!

[travelkind]

One little problem, it looks like if my data in my table is 67.5 (one decimal point) then it doesn't want to round correclty to two decimal points.  Here is an example:

 

35,JFM01,10/18/2009,16.11,Clear,601500898

35,LS01,10/19/2009,27.87,Clear,601500884

35,HS,10/18/2009,71.19,Clear,601500446

35,HGM01,10/18/2009,67.5,Clear,601500439

30,HCS,10/18/2009,66.56,Clear,601500322