rockyraw rockyraw - 4 months ago 14
MySQL Question

Percentage Calculation fails with with database data

I have a database with products, table1 contains current price, table2 contains previous price.

I want to calculate for each item, what is the difference in price, and what is that difference in percentage.

for some reason, the percentage calculation doesn't work and returns a zero.

To make sure my calculation itself is ok, I took the same product, and for the first instance I defined the item values after fetching the DB, and for the second instance the calculation is straight from DB.

Only the first scenario works fine, returing

-17%
difference.

Code:

<?php

/* database section start */
$mysqli = new mysqli("db","user","pass","db_name");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* database section end */

// Choose Relevant Products, and turn them into an array
$item_array = array(
'item1',
'item1'
);

//implode items, turn into string
$item_implode = join("','", $item_array);

//declare an overall array for result
$product = array();

$result = $mysqli->query("SELECT p.Name, p.now_price, h.was_price FROM table1 p LEFT JOIN table2 h ON p.ProductId = h.ProductId WHERE p.Name IN ('$item_implode') ORDER BY FIELD (p.Name, '$item_implode');");


while($row = $result->fetch_assoc()) {

$product_name = $row['Name'];
// find all keys in $item_array which value is the products
$keys = array_keys($item_array, $product_name);
foreach ($keys as $key) {

// add values for these keys
$product[$key + 1]["Name"] = $row['Name'];
$product[$key + 1]["now_price"] = $row['now_price'];//
$product[$key + 1]["was_price"] = $row['was_price'];
$product[$key + 1]["delta"] = $row['now_price']-$row['was_price'];
$product[$key + 1]["delta_percent"]= $row['delta']/$row['was_price'];


}
}

// Manual definition:

$product[1]["delta"]=$product[1]["now_price"]-$product[1]["was_price"];
$product[1]["delta_percent"]= $product[1]["delta"]/$product[1]["was_price"];


?>
<body>
<?php $i = 1; while ($i <= 2) { ?>
<ul class="listab">
<li>#: <?= $i ?></li>
<li>Now: <?= $product[$i]["now_price"] ?></li>
<li>Was: <?= $product[$i]["was_price"] ?></li>
<li>Diff: <?= $product[$i]["delta"] ?></li>
<li>Per: <?= $product[$i]["delta_percent"] ?></li>
</ul>
<?php $i = $i + 1; } ?>
</body>
</html>


Output:

#: 1
Now: 306.99
Was: 369.99
Diff: -63
Per: -0.170274872294

#: 2
Now: 306.99
Was: 369.99
Diff: -63
Per: 0

Answer

You have an error in this line:

    $product[$key + 1]["delta_percent"]= $row['delta']/$row['was_price'];

There's no $row['delta']. The previous statement calculated the delta, and it put it in $product[$key+1]["delta"]. So that should be:

    $product[$key + 1]["delta_percent"]= $product[$key+1]["delta"]/$row['was_price'];

BTW, this isn't a percentage, it's a ratio. To get a percentage, you have to multiply by 100.

You could also calculate the delta and ration in the SQL.

$result = $mysqli->query("
    SELECT p.Name, p.now_price, h.was_price, 
        p.now_price - h.was_price as delta, (p.now_price-h.was_price)/h.was_price AS delta_percent
    FROM table1 p 
    LEFT JOIN table2 h ON p.ProductId = h.ProductId 
    WHERE p.Name IN ('$item_implode') 
    ORDER BY FIELD (p.Name, '$item_implode');");