Math Function

[LinusIT]

Hi

I need to do some multiplication from the results returned from a database. At the moment I'm using the following query to return the results and then the next bit to display them

$sql1="SELECT * FROM pricelist WHERE pricelist_id=$company";
$result1 = mysql_query($sql1) or die(mysql_error());

while($row=mysql_fetch_array($result1)) {
<?php echo $row['copper_dry_bright']; ?>
<?php echo $row['alu_clean_he9']; ?>

And so on. Don't worry about the above code looking strange, it's just bits I've copied and pasted out of my page.

I could obviously do the math on each line but I'm wondering if it could be done in a function instead.

Manual method:

<?php $total1 = sprintf("%.2f", ($row['copper_dry_bright'] * 1.10)) echo $total1; ?>

Any ideas?

[penders]

Are you asking whether it is worth putting that one line of code into a function? If your code consists of not much more than what you have posted then no. However, if you are calling it from different parts of your code and there is a possibility the math might change then possibly.

Perhaps the whole thing should be in a function?

[LinusIT]

The number of results I need to work with would be around 50-60, hence thinking a function would be a good idea. The only other way of doing it would be todo that single line for each result returned.

Basically I have a table for a pricelist, I would like to take the result returned from the pricelist and multiple it by "whatever". The table is static html written and then the price is displayed using what I've posted above "<?php echo $row['whatever']; ?>".

[penders]

Is each result a column in your database? 'copper_dry_bright', 'alu_clean_he9', etc...? If so, then you would loop through your columns rather than doing it manually for each result/column (if that is what you are implying), or is that not how the results are stored?

$htm = ''; 
while ($row = mysql_fetch_array($result1)) { 
  $htm .= '<tr>'; 
  foreach ($row as $name => $value) { 
    $htm .= '<td title="'.$name.' / '.$value.'">'; 
    $htm .= sprintf("%.2f", ($value * 1.10)); 
    $htm .= '</td>'; 
  } 
  $htm .= '</tr>'; 
} 
echo $htm;

[LinusIT]

I have had to format the table manually in html to get the design I needed, this means I can't look through the results and display them that way.

Would it be helpful to post some html table code so you can see how I've done it?

[penders]

Yes please.

[httpwebwitch]

I'd do it this way:

define("TAX_RATE", 1.1);


function tax_included($amount) {
return $amount * TAX_RATE;
}


foreach... etc {
echo number_format(tax_included($amount) ,2);
}


Abstracting keeps your code tidy.

So, if the tax rate changes, you change the value of that defined constant - in one place.

If the formula for calculating tax changes (say, if it varies based on other factors) you can change the function that does the math.

And I like using number_format() instead of sprintf. You could just as easily use a custom function instead of number_format, maybe one that formats the currency according to locale or into different currencies.

Abstracting one-liners into functions isn't bad, as long as you're not doing it excessively to every little thing

[LinusIT]

Here's how I display the data at present:

<?php while($row=mysql_fetch_array($result1)) { ?>
<table cellpadding="0" cellspacing="0" id="data">
<tr>
<td>Dry Bright Wire</td>
<td><?php echo $row['copper_dry_bright']; ?></td>
<td>Clean HE9</td>
<td><?php echo $row['alu_clean_he9']; ?></td>
<td>Mixed Brass</td>
<td><?php echo $row['brass_mixed']; ?></td>
</tr>
<tr>
<td>Greasy Bright Wire</td>
<td><?php echo $row['copper_greasy_dry_bright']; ?></td>
<td>Painted HE9</td>
<td><?php echo $row['alu_painted_he9']; ?></td>
<td>Rod Brass Solids</td>
<td><?php echo $row['brass_rod_solids']; ?></td>
</tr>
<tr>
<td>Clean Flat Electro Bus Bar</td>
<td><?php echo $row['copper_electro']; ?></td>
<td>Thermal Break</td>
<td><?php echo $row['alu_thermal_break']; ?></td>
<td>No1 Brass Rod Borings</td>
<td><?php echo $row['brass_no1']; ?></td>
</tr>
</table>
<?php } ?>

It's quite a bit longer than that due to the number of different items but hopefully this should give you an idea.

It's for each of these items that I'd like to apply the multiplication too.

[penders]

Certainly if this is a tax calculation then that does lend itself to be abstracted out into its own function, since it is likely to be required in several places. However, I believe your HTML table output can be compacted into a much tighter loop...

It looks like your SELECT query returns just one record - is that right? You also have an ID on your output table, which can only occur once. Although you are using a while() loop to traverse the result set, which implies multiple records?

Anyway, assuming your descriptive column names aren't stored anywhere else, then you could store these in an associative array (in the order required) and loop through your columns, with 3 columns per row. This avoids having to repeat your HTML markup over and over.

<?php 
$cols = array ( 
  'copper_dry_bright' => 'Dry Bright Wire', 
  'alu_clean_he9' => 'Clean HE9', 
  'brass_mixed' => 'Mixed Brass', 
  'copper_greasy_dry_bright' => 'Greasy Bright Wire', 
  'alu_painted_he9' => 'Painted HE9', 
  'brass_rod_solids' => 'Rod Brass Solids', 
  'copper_electro' => 'Clean Flat Electro Bus Bar', 
  'alu_thermal_break' => 'Thermal Break', 
  'brass_no1' => 'No1 Brass Rod Borings', 
); 
$colsPerRow = 3; 
$htm = ''; 
$htm .= '<table border="1" cellpadding="5" cellspacing="0" id="data">'.PHP_EOL; 
while ($row = mysql_fetch_array($result1)) { 
  $htm .= '<tr>'; 
  $colCount = 0; 
  foreach ($cols as $colName => $colDesc) { 
    if (($colCount > 0) && ($colCount % $colsPerRow == 0)) { 
      $htm .= '</tr>'.PHP_EOL.'<tr>'; 
    } 
    $htm .= '<td>'.$colDesc.'</td>'; 
    $htm .= '<td>'.number_format($row[$colName]*1.10,2).'</td>'; 
    $colCount++; 
  } 
  $htm .= '</tr>'.PHP_EOL; 
} 
$htm .= '</table>'.PHP_EOL; 
echo $htm; 
?>

The addition of the PHP_EOL is just to make the source code look pretty and is not required.

The math calculation now only appears once in the source code, however, if you wish to have the calculation in its own function, as per httpwebwitch's suggestion, then replace:

$htm .= '<td>'.number_format($row[$colName]*1.10,2).'</td>';

With...

$htm .= '<td>'.number_format(tax_included($row[$colName]),2).'</td>';

[LinusIT]

I feel like I've wasted your time here, sorry! Not from what you've posted isn't useful, it is! I'll copy the whole table below so you can see what I'm dealing with and why I've done it through static HTML instead of using PHP. It's rather long so I do apologise for that..

<?php
while($row=mysql_fetch_array($result1)) {
echo "<h1 class=\"$color\">" . $row['pricelist_name'] . " Pricelist</h1>";
$date = $row['last_updated']; $last_updated = date("j F Y H:i", strtotime($date)); ?>
<table cellpadding="0" cellspacing="0" id="data">
<tr>
<td colspan="2" class="<?php echo $style; ?>">Copper</td>
<td colspan="2" class="<?php echo $style; ?>">Aluminium</td>
<td colspan="2" class="<?php echo $style; ?>">Brass &amp; Bronze</td>
</tr>
<tr class="odd">
<td class="border-left">Dry Bright Wire (Heavy Guage, Loose)</td>
<td class="border-right">&pound;<?php echo $row['copper_dry_bright']; ?></td>
<td>Clean HE9 (max 2ft - free of PVC film)</td>
<td class="border-right">&pound;<?php echo $row['alu_clean_he9']; ?></td>
<td>Mixed Brass (free from steel &amp; plastic)</td>
<td class="border-right">&pound;<?php echo $row['brass_mixed']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Greasy Bright Wire</td>
<td class="border-right">&pound;<?php echo $row['copper_greasy_dry_bright']; ?></td>
<td>Painted HE9 (max 2ft - free of PVC film)</td>
<td class="border-right">&pound;<?php echo $row['alu_painted_he9']; ?></td>
<td>Rod Brass Solids</td>
<td class="border-right">&pound;<?php echo $row['brass_rod_solids']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Clean Flat Electro Bus Bar</td>
<td class="border-right">&pound;<?php echo $row['copper_electro']; ?></td>
<td>Thermal Break</td>
<td class="border-right">&pound;<?php echo $row['alu_thermal_break']; ?></td>
<td>No1 Brass Rod Borings</td>
<td class="border-right">&pound;<?php echo $row['brass_no1']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Copper Stampings/Cuttings (new, oil free)</td>
<td class="border-right">&pound;<?php echo $row['copper_stampings']; ?></td>
<td>Clean Alloy Wheels (no valves, weights, tyres)</td>
<td class="border-right">&pound;<?php echo $row['alu_clean_alloy_wheels']; ?></td>
<td>No2 Brass Borings / Turnings</td>
<td class="border-right">&pound;<?php echo $row['brass_no2']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Tinned Electro &amp; Wire</td>
<td class="border-right">&pound;<?php echo $row['copper_tinned']; ?></td>
<td>Alloy Wheels (no tyres)</td>
<td class="border-right">&pound;<?php echo $row['alu_alloy_wheels']; ?></td>
<td>Brass Cuttings</td>
<td class="border-right">&pound;<?php echo $row['brass_cuttings']; ?></td>
</tr>
<tr class="even">
<td class="border-left">New Copper Tube (short)</td>
<td class="border-right">&pound;<?php echo $row['copper_new_tube']; ?></td>
<td>Alloy Radiators</td>
<td class="border-right">&pound;<?php echo $row['alu_alloy_radiators']; ?></td>
<td>Brass Copper Radiators (clean - no steel)</td>
<td class="border-right">&pound;<?php echo $row['brass_copper_rads']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Multi Strand Hard Drawn Wire</td>
<td class="border-right">&pound;<?php echo $row['copper_hard_drawn']; ?></td>
<td>Litho Sheets (paper free)</td>
<td class="border-right">&pound;<?php echo $row['alu_litho_sheets']; ?></td>
<td>Commercial PB Bushes/Bearings</td>
<td class="border-right">&pound;<?php echo $row['brass_commercial_pb']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Heavy Copper 98%</td>
<td class="border-right">&pound;<?php echo $row['copper_98']; ?></td>
<td>Commercial Pure Alloy Cuts</td>
<td class="border-right">&pound;<?php echo $row['alu_commercial_pure']; ?></td>
<td>Gun Metal Solids<br />(free of excess brass &amp; steel)</td>
<td class="border-right">&pound;<?php echo $row['brass_gun_metal_solids']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">No1 Copper Wire / Old Electro</td>
<td class="border-right">&pound;<?php echo $row['copper_no1']; ?></td>
<td>Quadrant / Mix Cuts</td>
<td class="border-right">&pound;<?php echo $row['alu_poly_cuts']; ?></td>
<td>Gun Metal Borings<br />(clean &amp; dry - free of excess brass &amp; steel)</td>
<td class="border-right">&pound;<?php echo $row['brass_gun_metal_borings']; ?></td>
</tr>
<tr class="even">
<td class="border-left">No2 Copper Wire / Copper Turnings</td>
<td class="border-right">&pound;<?php echo $row['copper_no2']; ?></td>
<td>Cast Aluminium</td>
<td class="border-right">&pound;<?php echo $row['alu_cast_alloy']; ?></td>
<td>AB Solids</td>
<td class="border-right">&pound;<?php echo $row['brass_ab_solids']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Tanks (element, foam &amp; scale free)</td>
<td class="border-right">&pound;<?php echo $row['copper_tanks']; ?></td>
<td>Old Rolled Aluminium (no cans, no ali rads)</td>
<td class="border-right">&pound;<?php echo $row['alu_old_rolled']; ?></td>
<td>AB Swarf</td>
<td class="border-right">&pound;<?php echo $row['brass_ab_swarf']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Braziery Copper</td>
<td class="border-right">&pound;<?php echo $row['copper_braziery']; ?></td>
<td>Aluminium Turnings</td>
<td class="border-right">&pound;<?php echo $row['alu_turnings']; ?></td>
<td>PB Swarf</td>
<td class="border-right">&pound;<?php echo $row['brass_pb_swarf']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Clean Pyro Cable (loose)</td>
<td class="border-right">&pound;<?php echo $row['copper_clean_pyro']; ?></td>
<td>Ali/Copper Radiators (clean, loose No Corrosion)</td>
<td class="border-right">&pound;<?php echo $row['alu_copper_rads']; ?></td>
<td colspan="2" class="<?php echo $style; ?>">Stainless Steel</td>
</tr>
<tr class="even">
<td class="border-left">PVC Pyro Cable (loose)</td>
<td class="border-right">&pound;<?php echo $row['copper_pvc_pyro']; ?></td>
<td>Irony Aluminum</td>
<td class="border-right">&pound;<?php echo $row['alu_irony_alloy']; ?></td>
<td>18/8 Stainless Steel Solids (under 5ft)</td>
<td class="border-right">&pound;<?php echo $row['ss_188_solids']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Copper Elements</td>
<td class="border-right">&pound;<?php echo $row['copper_elements']; ?></td>
<td>Zinc</td>
<td class="border-right">&pound;<?php echo $row['alu_zinc']; ?></td>
<td>18/8 Stainless Steel Turnings</td>
<td class="border-right">&pound;<?php echo $row['ss_188_turnings']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Electric Motors (free of pumps)</td>
<td class="border-right">&pound;<?php echo $row['copper_electric_motors']; ?></td>
<td colspan="2" class="<?php echo $style; ?>">Lead</td>
<td>316 Stainless Steel Solids (under 5ft)</td>
<td class="border-right">&pound;<?php echo $row['ss_316_solids']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Fridge Motors</td>
<td class="border-right">&pound;<?php echo $row['copper_fridge_motors']; ?></td>
<td>Clean Lead Scrap</td>
<td class="border-right">&pound;<?php echo $row['lead_clean_lead']; ?></td>
<td>316 Stainless Steel Turnings</td>
<td class="border-right">&pound;<?php echo $row['ss_316_turnings']; ?></td>
</tr>
<tr class="even">
<td class="border-left">High Grade</td>
<td class="border-right">&pound;<?php echo $row['copper_high_grade']; ?></td>
<td>Contaminated Lead Scrap</td>
<td class="border-right">&pound;<?php echo $row['lead_contaminated_lead']; ?></td>
<td>Chrome Iron</td>
<td class="border-right">&pound;<?php echo $row['ss_chrome_iron']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Household Cable (free of fittings/wood)</td>
<td class="border-right">&pound;<?php echo $row['copper_household']; ?></td>
<td>Lead/Acid Batteries (free of steel)</td>
<td class="border-right">&pound;<?php echo $row['lead_acid_batteries']; ?></td>
<td colspan="2" class="<?php echo $style; ?>">Other</td>
</tr>
<tr class="even">
<td class="border-left">Lead Armoured Greasy Copper Cable</td>
<td class="border-right">&pound;<?php echo $row['copper_lead_armoured_greasy']; ?></td>
<td>&nbsp;</td>
<td class="border-right">&nbsp;</td>
<td>Cars</td>
<td class="border-right">&pound;<?php echo $row['other_cars']; ?></td>
</tr>
<tr class="odd">
<td class="border-left">Low Grade Armoured Cable</td>
<td class="border-right">&pound;<?php echo $row['copper_low_grade_armoured']; ?></td>
<td>&nbsp;</td>
<td class="border-right">&nbsp;</td>
<td>Catalytic Converters (Trimmed)</td>
<td class="border-right">&pound;<?php echo $row['other_catalytic_converters']; ?></td>
</tr>
<tr class="even">
<td class="border-left">&nbsp;</td>
<td class="border-right">&nbsp;</td>
<td>&nbsp;</td>
<td class="border-right">&nbsp;</td>
<td>Engines (Rip Outs)</td>
<td class="border-right">&pound;<?php echo $row['other_engines']; ?></td>
</tr>
<tr>
<td colspan="6" class="<?php echo $style; ?>">Iron</td>
</tr>
<tr class="odd">
<td class="border-left">No 1 &amp; 2</td>
<td>&pound;<?php echo $row['iron_no1_no2']; ?></td>
<td>Light Iron</td>
<td>&pound;<?php echo $row['iron_light_iron']; ?></td>
<td>Profile (Over Size)</td>
<td class="border-right">&pound;<?php echo $row['iron_profile']; ?></td>
</tr>
<tr class="even">
<td class="border-left">Burnings (Over Size)</td>
<td>&pound;<?php echo $row['iron_burnings']; ?></td>
<td>Plate &amp; Grider (Over Size)</td>
<td>&pound;<?php echo $row['iron_pg1']; ?></td>
<td>Turnings</td>
<td class="border-right">&pound;<?php echo $row['iron_turnings']; ?></td>
</tr>
<tr class="odd">
<td class="border-leftbottom">Cylinder Iron</td>
<td class="border-bottom">&pound;<?php echo $row['iron_cylinder_iron']; ?></td>
<td class="border-bottom">Plate &amp; Grider (VA)</td>
<td class="border-bottom">&pound;<?php echo $row['iron_pg2']; ?></td>
<td class="border-bottom">Shearing (Over Size)</td>
<td class="border-rightbottom">&pound;<?php echo $row['iron_shearings']; ?></td>
</tr>
<tr>
<td colspan="6" class="text-right"><em>Last Updated: <?php echo $last_updated; ?></em></td>
</tr>
</table>
<?php } ?>

Your correct there, my select query does only return one record. Am I doing something wrong by using a while loop?

[penders]

Ah OK, there appears to be a few things there that break the pattern which would make it a bit tricky. Yes, to answer your original query, a function to perform your calculation and (as httpwebwitch suggests) perhaps another to return a monetary amount (including money symbol) would certainly help...

<td class="border-right">&pound;<?php echo $row['ss_316_turnings']; ?></td>

<td class="border-right"><?=moneyIncTaxFormat($row['ss_316_turnings'])?></td>

Just curious, regarding your CSS, are you using the

border-collapse:collapse

border model?

[LinusIT]

Awesome, working great now! Thanks

Yes I'm using the border collapse model, I'm pretty sure I am anyway.

I've got one more question on this subject. Now that the results are doing whatever math I specify is it possible for the results to be rounded down to the nearest 5?

I've got it to "floor" the results so there are no decimal points but I'd like the result "1234" to become "1230".

Thanks

[httpwebwitch]

round down to the nearest 5:


$x = floor($x / 5) * 5;

[LinusIT]

Am I doing something wrong here:

function PriceRate($amount) {
return floor((($amount * TAX_RATE) / 5) * 5);
}

One of the results returns 1232 which I would like to become 1230.

[penders]

return floor((($amount * TAX_RATE) / 5) * 5)

Should be...

return floor(($amount * TAX_RATE) / 5) * 5;

...to apply floor() to the division part, not the entire calculation.

[LinusIT]

Fantastic, it's working now! Thank you