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
best way to deal with prices on a database?
Moderator: General Moderators
-
shawngoldw
- Forum Contributor
- Posts: 212
- Joined: Mon Apr 05, 2010 3:38 pm
Re: best way to deal with prices on a database?
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.
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.
Re: best way to deal with prices on a database?
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?
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:
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
Code: Select all
function currency($cents, $dec=2)
{
return "$ " . number_format($cents / 100, $dec, '.', ',');
}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