[RESOLVED] Excel Formulas to PHP

[maner]

First let me say, this place rocks. I just found it and have gained a lot of insight into programming in PHP.

I am fairly new to PHP... That being said I can do some basic programming, having read a few books on PHP. I am finding that actually 'DOING' original work is a bit more difficult than the examples.

I have an Excel Spreadsheet that I use to build Configurations by picking the parts needed for the total install. Each part has a Quantity, Part Number, Description, Price and a Percentage Markup that is refered to over and over again. I am looking for a way to duplicate this spreadsheet in a PHP app.

Here's a an example...
Qty Item Part # Price w/ Disc.
0 Part Description for Part #1 11111111 1050.00 -1050.00

Install Maint Cost Discount
56.00 8.00 537.73 5%

The 'w/ Disc.' is figured in Excel this way: =SUM(F5*M108)-F5 F5 is the Price, M108 is the Discount

Also I need to be able to have the user specify the Quantity and the Discount amount and display the result in a new page after the form is submitted.

Can someone give me an example of how to do this in PHP? I appreciate all the help 🙂

[bpat1434]

Well, the math is strictly the same, and yet slightly different:

<?php
$install = 56;
$main = 8;
$cost = 537.73;
$discount = 5; // We know it's a percent, so we'll use percent*100 format....

$with_disc = ($cost*(round($discount/100, 2))-$cost;
echo $with_disc;
?>

Now, if you store your information in a mySQL database, it will be slightly different, but that's the basics of it.

You can read more on what mathematical functions there are HERE

[maner]

I appreciate the quick response.

I think I understand how your formula works...
Divide the Discount by 100, round the result, multiply by cost then subtract the cost. Is this correct and what is the 2 for after the division of the discount by 100?

Also when I paste the code into a new page in dreamweaver then synch I get no echo of the result. Any ideas?

thanks for all the help 🙂

[bpat1434]

New code

<?php
$install = 56;
$main = 8;
$cost = 537.73;
$discount = 5; // We know it's a percent, so we'll use percent*100 format....

$with_disc = ($cost*(round($discount/100, 2)))-$cost;
echo $with_disc;
?> 

Should have counted the closing ")" in the mathematics...

Anyway, the math is set up like you explained it. The "2" as the second argument in the round() function defines how many past the decimal to round. Mainly since we deal with percents (5, 10, 15) we won't need more than 2 past it. You can increase or decrease this as you please. More info can be found in the manual....

[maner]

DOH! I missed it too 🙂

Any idea how to let the user specify the QTY in a field and multiply the result by that?

BTW, thanks 🙂

[maner]

...and shorten the resulting figure to 2 decimal places?

[bpat1434]

sure, just post the form to the php page, replace whatever variables value ($install?) with $_POST['text-box-name'] and go!!

And [man]round/man can handle limiting decimal places (as stated above)....

<?php
if(!isset($_POST['qty']) || empty($_POST['qty']))
{
    $error['present'] = 1;  // We have an error
    $error['errors'] = array('quantity');
    $qty = 1;
}
else
{
    $error = 0;  // No errors
    $qty = $_POST['qty'];
}

$install = $qty;  // guessing install is how many they want installed
$main = 8;
$cost = 537.73;
$discount = 5; // We know it's a percent, so we'll use percent*100 format....

$with_disc = round( (($cost*(round($discount/100, 2)))-$cost), 2); // make answer in 2 decimal places
if($error['present'] == 1)
{
    // Let them know we used a default value:
    echo 'You failed to submit a quantity.  We defaulted to a quantity of 1.<br><br>';
}
echo $with_disc;

if(!isset($_POST) || $error['present'] == TRUE)
{
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<p<?php
if($error['present'] == TRUE && in_array('quantity', $error['errors']))
{
?>
 style="padding: 5px; border: 2px groove #900; background-color: #f66;"
<?php
}
?>>
Quantity: <input type="text" name="qty"></p>
<p><input type="submit" value="Order" name="submit"></p>
</form>
<?php
}
?>

Something like that....

[maner]

U absolutly rock m8 🙂

I can definately work with this, I really do appreciate it.

[bpat1434]

sweet, glad I could help!!

[maner]

I am having some trouble here. I cant seem to figure out why this will not work, when I view it in a browser I get nothing... Thanks in advance for the help 🙂

<?php 
$qty = 1;
$item = 'Product Name 1';
$pec = 000001; // Secondary Part Number
$sap = 000111; // Part Number
$price = 1000.00; // Standard Price
$install = 50.00; // Install Cost
$maint = 5.00; // Maintenace Cost
$discount = 5.00; // Percentage, use percent*100 format
$ext_price = 1000.00; // Standard Price * Qty
$ext_install = 50.00; // Install Cost * Qty
$ext_maint = 0.00; // Maintenace Cost * Qty
$ext_usc = 0.00 // Price after Discount
$ext_discount = 5.00; // Percentage, use percent*100 format

echo $qty;
echo $item;
echo $pec
echo $sap
echo $price
echo $install
echo $maint
echo $discount
$with_disc = round( (($price*(round($discount/100, 2)))-$cost), 2); 
echo $with_disc; 
$with_ext_price = round( (round($ext_price*$qty)), 2;
echo $with_ext_price
$with_ext_install = round( (round($ext_install*$qty)), 2;
echo $ext_install
$with_ext_maint = round( (round($ext_maint*$qty)), 2;
echo $ext_maint
$with_disc = round( (($price*(round($discount/100, 2)))-$cost), 2); 
echo $with_disc; 
?> 

[bpat1434]

$ext_usc = 0.00

echo $pec
echo $sap
echo $price
echo $install
echo $maint
echo $discount

You need to end the lines with ";".....

check through all your code....

[maner]

can't believe I did that 🙁

FOr some reason I am still not getting any output tho... Im using IE7 Beta, that may be an issue, I'll D/L FIrefox and see if that works. Any other ideas about what the issue may be?

[bpat1434]

you still have errors.....

If you're running this from a dev-box on your own server, use the error_reporting levels of E_ALL & E_STRICT

Look closely at your function calls, there are some errors in there.

[maner]

I found the issue, it was with my function calls just as you said. Thanks for that 🙂

I now find that the results are jammed together, are there any good reference points on how to format results?

[bpat1434]

string concatenation:
$var.' some string to separate '.$var2.' some string to separate '.$var3;

You can read more on concatenation here and here.