How to work out 5% of a unqiue number in SQL

1895 views php
1

I currently have a panel where admins can upload CSV files with certain data on them. I would like it so that once they upload a CSV file it will take the balance column and work out 5% of that number and then update that specific record with the 5% value in a column called price.

I have the following code however it updates them all with the same 5% value although I have many records with different balances, Any help?:

$stmt = $auth_user->runQuery("SELECT * FROM `ab` WHERE `purchased_by` = 0");
$stmt->execute(array($_SESSION['user_session']));
while ($getBalance = $stmt -> fetch(PDO::FETCH_ASSOC)){
    $balance = $getBalance['balance'];
}
function getPercentOfNumber($number, $percent){
    return ($percent / 100) * $number;
}
$price = getPercentOfNumber($balance, 5);
$stmt = $auth_user->runQuery("UPDATE `ab` SET `price` = :price WHERE `purchased_by` = 0");
$stmt->execute(array(":price" => $price, ":balance" => $balance));

answered question

...and I don't understand the point of the SELECT.

If you want to update all rows that match that condition, you only need 1 query.

It's a little confusing how you balances are to be used here. You have a while loop that gets a balance but does so each time through the loop without using the balance value anywhere. So you are getting each balance but only keeping the last one.

In your second query, you bind two parameters, but only assign one parameter.

1 Answer

7

As written, this query will update all rows having purchased_by = 0, which is undoubtedly not what you want.

As you loop through the result, use the primary key value of the row in your UPDATE statement so that only this row will be updated.

posted this

Have an answer?

JD

Please login first before posting an answer.