Help with getting correct amount into table column

What I’m trying to accomplish, is that every time an amount gets updated to the uploaders ‘balance’ column, half that amount gets updated(added to) into the uploaders ‘money’ column. However, using the code below, when the amount ‘3’ is added to the ‘balance’ column, just ‘1’ gets added to the ‘money’ column, instead of the intended amount of 1.5. And both columns have the same table structure:

$up_amount = $video_cost_new *0.50;

// add to the balance
$uploader_account = $up_user_start->balance+$up_amount;

$money_amount = $up_amount / 2;
$up_user_start = $db->where('id', $video->user_id)->getOne(T_USERS);
$money_account = $up_user_start->money+$money_amount;
$db->where('id', $video->user_id);
$update_money = $db->update(T_USERS, [
'money' => number_format($money_account, 1, '.', ''), ]);

Can you suggest what I can change to get it correct? You guidance is appreciated.

what field type is the money column in the table?

thanks for your reply.
Both the ‘balance’ and ‘money’ columns are varchar(100)

Where does the rounding problem start? That is, is $up_amount correct, and is $money_amount correct? Wouldn’t those columns that store balances be better as numeric columns?

I’m not familiar with the library you’re using to access and update the users table, but it seems to me as if there’s a potential for the money column to become inaccurate, due to the time between reading the original value of money, and then running a second separate query to store the new value with the extra balance on it.

Of course, I don’t know how likely it is that more than one user would be encountering this code for the same user and the same video at the same time. But it seems to me that a query along the lines of

update user-table set money = money + money_amount where user_id = ?

would remove the chance that two users would both hit that same code one line apart, meaning that the second user would over-write the balance update that the first user just performed. Of course, if your library is doing some kind of transaction processing where the whole lot is done in one operation, the above won’t come into play.

Thanks for your reply.
The ‘balance’ code & column appear to work successfully, which tells me $up_amount is correct. $money_amount doesn’t populate the ‘money’ column with the correct amount. For example, ‘1’ gets added to the ‘money’ column, instead of the intended amount of 1.5
Can you make a suggestion of what to change to try?

If the balance column updates correctly by adding 3, and the money column does not update correctly when you have a fractional value involved, does that suggest that the issue is with fractions?

What is actually in $money_amount before you try to update the database with it, if you display it somewhere or log it? Not what should it have, but what does it actually have? If it has the correct amount, 1.5 in this case, then that leads you to the update code being the problem.

I’ve had a quick go on a PHP tester to start with ‘6’, then multiply by 0.5, then divide by 2 (why not do it the same way, by the way?) and all of them end up with 1.5 as the result, so I can’t see why your code is any different.

Do any of the working columns of the same type (varchar) also contain fractional parts and work correctly?

Thanks for your reply. Greatly appreciated.
Regarding “What is actually in $money_amount before you try to update the database with it, if you display it somewhere or log it?” How can I display it or log it? I tried echo, but didn’t see anything, not sure if I did it correctly, but couldn’t find an output. I look forward to help with that.

Also, he’s my latest attempt. Maybe if you kindly took a second to a glance over the code, you might see the issue, here:

I look forward to hearing back from you. Much thanks again

I think you should be using bcmath functions.

Using a precision of 4 is a pretty safe bet for most monetary calculations.

Could you please provide a high level overview of what you have going on. Also, why is half of a deposit going somewhere else? Does this work anything like a banking app would work?

Thanks for all the replies. It appears to be working successfully now

Perhaps you could post what you did to solve the problem, in case it helps anyone else reading this thread in the future who has a similar problem.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.