best way to deal with prices on a database?

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
fael097
Forum Commoner
Posts: 34
Joined: Sat Mar 06, 2010 7:57 pm

best way to deal with prices on a database?

Post by fael097 »

hi, im making a database for a really simple e commerce, and i need to publish products, for that ill have to store their prices on the database. i tried storing them with dots for the cents, but if i sort by price, it will return weird results like 530.00 510.00 50.00 490.00 (it will put the 50.00 between 500 and 400, like 50 was the same as 500) so dot is not an option.
also, making a field for the value and another for the cents, sounds strange, but so far is the only option. anyone have better ideas?

i also need a way to make users type the cents value on the price field, like a javascript that automatically adds a dot for the last 2 digits when you type something. also need better ideas on this.

thanks in advance
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

Re: best way to deal with prices on a database?

Post by shawngoldw »

What did you store them as? varchars?

Anyways, I don't know if it's the BEST solution but you can store them as ints with the cents included and then insert the decimal point when you need to display the number. For example:

50.95 would be stored as 5095

When you need to display the number you insert the decimal before the 95 since you know it will always appear as the third last character from the end. These should also always sort properly.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: best way to deal with prices on a database?

Post by Jade »

Use a Decimal type, that's exactly what it's there for: http://dev.mysql.com/doc/refman/5.1/en/ ... types.html
websitesca
Forum Newbie
Posts: 16
Joined: Fri Jul 09, 2010 2:47 pm

Re: best way to deal with prices on a database?

Post by websitesca »

I find that using regular INT works great. I don't like to use DECIMAL because of weirdness with rounding if you perform any division or multiplication by a float. I always store the number is cents. Then I use a utility function to convert to the proper display format on the website. For example:

Code: Select all

function currency($cents, $dec=2)
{
    return "$ " . number_format($cents / 100, $dec, '.', ',');
}
I can't quite remember what the problems were with using DECIMAL, I was probably just doing it wrong. But using INT and storing in cents has always worked for me.

If you're storing stock prices with accuracy beyond just cents, perhaps DECIMAL would be better for you in that case.

Hope that helps you!
Georges,
http://www.websites.ca
Post Reply