Quick SQL roundup question for prices

[NodsDorf]

Is there an SQL function I can use to RoundUp products_price to 2 decimal places?

 

When we imported our products prices we used products price breaks and used a division function to get the discounts and extended the decimal places to 4. Unfortunately, Oscommerce doesn't handle the line totals and order totals the same way. This caused items to have different line totals then the actual price. To fix this issue I need to round UP all prices to the tenth.

 

IE.

.1744 = .18

.1770 =.18

1.111 = 1.12

 

I first thought just changing the database structure to decimal (4,2) but I think that would true round the figures .1744 would become .17, but I need to round up to the nearest penny .18..

 

My other alternative would be to re-upload all products and price breaks which would take several hours. If there is a roundup function I can use straight in SQL I would love to see an example.

Thanks,

Don

 

 

PS I did google it first and found only ways to round specific values. Unfortunately, we have thousands of different values so this would be counter productive.

[burt]

Make a PHP script to grab prices from database, update them and save back to database.

 

function roundup($price) {
   return ceil($price * pow(10, 2)) / pow(10, 2);
}

 

echo roundup('.1744');

 

Direct MySQL into phpmyadmin, I don't think there is something that can achieve the same.

[NodsDorf]

Make a PHP script to grab prices from database, update them and save back to database.

 

function roundup($price) {
return ceil($price * pow(10, 2)) / pow(10, 2);
}

 

echo roundup('.1744');

 

Direct MySQL into phpmyadmin, I don't think there is something that can achieve the same.

 

Thanks Burt, I was certain there was a function to do this in straight MySQL. My next step was to use php to update the database.

 

Thankfully a SQL author replied to a post on another forum and directed me to the right answer albeit without giving it to me directly which I really appreciate.

The solution:

UPDATE products_price_break SET products_price=(CEIL(products_price * 100) / 100) WHERE products_price > "0.0000" and / or

UPDATE products SET products_price=(CEIL(products_price * 100) / 100) WHERE products_price > "0.0000"