MySQL money decimal help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

MySQL money decimal help

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL money decimal help

Post by requinix »

Just round the number to however many decimal places you want. Or let MySQL do it for you.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: MySQL money decimal help

Post by synical21 »

how can mysql do it for me?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL money decimal help

Post by requinix »

When inserting or updating, pretend the number is in 10.2 format. Don't have to do anything special.
mybikeisgreen
Forum Newbie
Posts: 22
Joined: Thu Oct 01, 2009 6:22 pm

Re: MySQL money decimal help

Post 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.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: MySQL money decimal help

Post by synical21 »

Sorry i still dont understand how i can fix my problem :banghead: 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;
 
}
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: MySQL money decimal help

Post 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
Post Reply