Forcing insignificant number 1

[proleno]

Hi

I hope somebody can help. Currently we are using a SQL query to show euro prices on our website in addition to our current GBP and USD prices. We are then using a further query to show the euro price to two significant decimal places. The query we are using for this is:

UPDATE productsTable SET productAux1 = Round (productAux1,2);

The downside of this is that if we have a price of, say, EUR11.50 it is unfortunately only shown as EUR11.5 when using the above query.

Does anyone know of a SQL query similar to the above that I can use to force the display to two insignificant places so we end up with EUR11.50 etc?

Many thanks!

Andy

 

[r937]

use the FORMAT function

r937.com | rudy.ca

 

[proleno]

I'm sorry but I don't understnad what you mean....could you elaborate?

Thanks

 

[r937]

i mean the FORMAT function in mysql

it is described on this page of the mysql manual

r937.com | rudy.ca

 

[proleno]

Oh wow!!..... I see what you mean but this assumes that I have any inkling whatsoever of what any of that actually means....sadly, that all goes way over my head!

I was provided with the original SQL query by somebody who obviously did understand it but I am just a mere end user...and I think that this is too much for my three remaining brain cells to deal with!!

I'll see if I can find a forum for complete SQL numpties!!

 

[r937]

i don't think you need a different forum, but i do believe you need to hire somebody (hey, it could be just on a part-time contract basis) who can help you

r937.com | rudy.ca

 

[proleno]

err....well I can't imagine that I need to hire somebody to produce one SQL query...I managed the rest of the site with relative ease so I guess if I can't find anyone who can tell what the query should be then I will just have to wade through the manual until it becomes clear.

thanks for your help.

 

[TonyGroves]

You need to hire somebody"! I wonder who Rudy has in mind?

When you retrieve the value from the database, that's the time to worry about rounding and formatting. You would use something like:[tt]
SELECT FORMAT(productaux1,2) FROM productstable ...
[/tt]
You might like to have a look at your database structure while you're at it. Are you storing three different prices (euro, dollar and pound) in each product record? You would most likely be better off storing just one price, then multiplying by the current conversion rate only when you are about to display the value.

 

[proleno]

Thanks for your advice Tony....much appreciated...I shall have a play and see what I can achieve!