"shaving" zeros off the end 2

[compudude86]

i want to remove the trailing numbers from my columns, lets say my price, for example, is 199.00, i get 199.0000. i was told to use the "ROUND(price,2)" but how would i apply that to the 5 or so columns? what would be my syntax?

 

[guelphdad]

are you removing them entirely or just for display purposes and keeping the records as is?

If permanently then you use an UPDATE along with the ROUND.

If for display purposes then you use that ROUND for each column name (replacing price as the column name).

 

[compudude86]

ok, but what syntax would i use? i tried selecting a column in the query browser, then executing ROUND (XXXXX, 2) and it said i had a problem in the syntax

 

[guelphdad]

select
ROUND(price,2)
from yourtablename

 

[johnwm]

You can set the datatypes for the columns to DECIMAL so that the data is initially stored with the required precision and number of decimal places. See the manual:

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[compudude86]

ok, i figured out i could set it in mysql administrator. i just needed to change it form DECIMAL(19,4) to DECIMAL(19,2). now i know i should start another thread, but i dont want to fill up the forum with my questions, real quick, how do i set my fields to have the $ in it?

 

[guelphdad]

you wouldn't put the $ in your fields. you are storing numeric information not string information. To display the $ with your fields then you would use the CONCAT operator like this.

select
concat ('$',yourcolumn)
from yourtable

 

[compudude86]

im getting errors when i try to run the last one there

 

[r937]

try it without the space after CONCAT

r937.com | rudy.ca

 

[compudude86]

ok, that worked, but is it only temoporary or can i add it to each item permanently?

 

[guelphdad]

You aren't adding it to your table, you would add it every time you wanted to query your data and show the $ symbol in front of it.

Sorry about the space, I missed that.

 

[compudude86]

thats ok, i guess ill just add it in on my http frontend. thanks