Removing trailing zeros or decimals from a SQL decimal(7,3)

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
User avatar
edawson003
Forum Contributor
Posts: 133
Joined: Thu Aug 20, 2009 6:34 am
Location: Los Angeles, CA - USA

Removing trailing zeros or decimals from a SQL decimal(7,3)

Post by edawson003 »

I have field that I need to store in my database as a decimal(7,3) data point, but I would like to display on my front end as a one decimal place number or no decimal if there are no integers other than zero after the decimal.. Any thoughts?

I found this on teh net, but it didn't work:
SELECT replace(rtrim(replace(replace(rtrim(replace(field,'0',' '))
,' ','0'),'.',' ')),' ','.') from table
Any thoughts?
Last edited by edawson003 on Thu Oct 01, 2009 9:11 pm, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: converting decimal(7,3) data point to an int?

Post by requinix »

Use round: PHP will handle the conversions.
User avatar
edawson003
Forum Contributor
Posts: 133
Joined: Thu Aug 20, 2009 6:34 am
Location: Los Angeles, CA - USA

Re: converting decimal(7,3) data point to an int?

Post by edawson003 »

Actually, what I want is for the value that is stored in my SQL table to display as number with no decimal places for whole numbers and display the nearest tenth for decimal numbers.

For example:
150.000 should display as 150
169.450 should display as 169.5
20.340 should display as 20.4

Wouldn't applying the round() function just affect the digits. (i.e. 169.450 to 169.500)
User avatar
edawson003
Forum Contributor
Posts: 133
Joined: Thu Aug 20, 2009 6:34 am
Location: Los Angeles, CA - USA

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Post by edawson003 »

On second thought:
replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') FROM table
does work. I just need to add a field alias, like so:
replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') AS field
so my mysql_fetch_array pick up the value by name.

Code: Select all

$lastentryquery = "SELECT replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') AS fieldFROM table WHERE enteredby = $enteredby ORDER BY entrydt DESC LIMIT 1"; 
$userresult = mysql_query($lastentryquery);
while($row = mysql_fetch_array($userresult)){
$fielddisplay = $row['field'];
I guess I answered my own post...gnarly!

Just thought I'd close the loop on how I resolved my issue so others to reference.
Last edited by edawson003 on Thu Oct 01, 2009 10:20 pm, edited 1 time in total.
jmaker
Forum Newbie
Posts: 16
Joined: Tue May 21, 2002 11:13 pm

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Post by jmaker »

Code: Select all

 
echo round(150.000, 1);  // prints 150
echo round(169.450, 1);  // prints 169.5
echo round(20.340, 1);    // prints 20.3
 
User avatar
edawson003
Forum Contributor
Posts: 133
Joined: Thu Aug 20, 2009 6:34 am
Location: Los Angeles, CA - USA

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Post by edawson003 »

Or... I could just use

Code: Select all

echo round(field, 1);


Thx, jmaker!

I'll just scoot back to my newbie corner. :oops:
Post Reply