Page 1 of 1
MySQL money decimal help
Posted: Thu Oct 01, 2009 2:44 pm
by synical21
Just a little problem i am having in my database, i have user_money fields which are set as TYPE: decimal and the length is (10,2) But when i do money calculations in php the money has ended up as numbers such as $1.9665 ... So the question is how can i make a field specificly for money which keeps the digits at 9.99
Thanks
Re: MySQL money decimal help
Posted: Thu Oct 01, 2009 3:38 pm
by requinix
Just
round the number to however many decimal places you want. Or let MySQL do it for you.
Re: MySQL money decimal help
Posted: Thu Oct 01, 2009 4:49 pm
by synical21
how can mysql do it for me?
Re: MySQL money decimal help
Posted: Thu Oct 01, 2009 6:03 pm
by requinix
When inserting or updating, pretend the number is in 10.2 format. Don't have to do anything special.
Re: MySQL money decimal help
Posted: Thu Oct 01, 2009 6:56 pm
by mybikeisgreen
before doing calculations of multiplication and/or division, multiply the number by 100 effectively making the number into an integer. Use ceil() or floor() to avoid any fractions. Then after the multiplication and/or division, divide it by 100 again.
Re: MySQL money decimal help
Posted: Fri Oct 02, 2009 10:14 am
by synical21
Sorry i still dont understand how i can fix my problem

ill show you an example of the equation where the numbers mess up:
Code: Select all
$total_cost = ($_POST['amountworkers'] + $_POST['perperson']) * 1.05;
$user_cost = $_SESSION['user_money'];
if($total_cost > $user_cost)
{
die("You have insufficient funds to create this job ");
}
else{
$_SESSION['user_money'] = $user_cost - $total_cost;
}
Re: MySQL money decimal help
Posted: Fri Oct 02, 2009 4:14 pm
by synical21
I know it has to use the round function but i dont know how to write it correctly in the script with variables
Code: Select all
$total_cost = round($_POST['amountworkers'] + $_POST['perperson'] ,2) * round(1.05, 2);
$user_cost = $_SESSION['user_money'];
if($total_cost > $user_cost)
{
die("You have insufficient funds to create this job ");
}
else{
$_SESSION['user_money'] = round($user_cost - $total_cost, 2);
}
Is my first attempt is this right?
EDIT with some adjustments it works, problem solved