mysql data type question

[MDGM]

Hi all,

I need to save a number to 3 decimal points, however I want 10.3 to be saved like this:

10.3

not this:

10.300

varchar will not work because ORDER BY rating treats 10 as being lower than 9... So what datatype should I use?

Thanks,

Max.

[darkagn]

I would use

DECIMAL(10,3)

This is a number with a total of 10 digits, up to 3 of which can be stored after the decimal. Adjust the 10 as needed, it is the default number used in the DECIMAL type in MySQL.

[MDGM]

I have tried decimal, but it always adds 0's onto the end of my number.

I want it to only add the exact number I enter in, so for example at the moment if i enter in 10, it changes it to 10.000 whereas I want it just to save 10...

Max

[almostbob]

I have tried decimal, but it always adds 0's onto the end of my number.

I want it to only add the exact number I enter in, so for example at the moment if i enter in 10, it changes it to 10.000 whereas I want it just to save 10...

Max

data dump an sql database
nothing is stored as entered,
text fields are padded,
numbers are stored to noted precision,
numerically there is no difference between 10 and 10.000
If you want to be able to enter 3.331 then 10 becomes 10.000
if you are trying to establish data precision use a different database structure,
something like, an field populated by your insert statement value= number of entered digits right of the decimal point will set precision for each entered item,
can be used to notify user that their 4 decimal number will be rounded to 3 in the database, etc, etc
its only a design issue

[MDGM]

okay, so if I wanted to echo 10.000 as 10, I would have to just shave off the appended 0's?

How would I do this? Can anybody post a simple script to remove the appended 0's?

Max

[uncle_smith]

I think if you cast to integer, php will automatically do this for you.
$val = '10.000';
$val = (int)$val;

$val should become just 10 now.

You can also try printf or sprintf functions to format your string
and if you want to preserve the decimal points then try
floatval()

for example floatval('10.300') should result in 10.3

[diafol]

Have you read the mathematical functions and expressions section of the php manual? There are many ways to round numbers (e.g.

ceil(), floor(), round()

in addition to the others quoted previously).

Are you looking to produce a number correct to a specific number of significant figures or to a specific number of decimal places?

If you want to change a number so that it has a MAXIMUM number of decimal places = 3, then do something this:

echo round($num,3);

If you have trailing 0's they will be trimmed automatically.

[almostbob]

if the number in the database is $number
php can handle it as a string anyway
the number will be stored in the database as *.???,
by the predefined data format decimal(10,3)
exactly 3 digits after the point,
and trailing digits=0 are insignificant, rtrim ( $number, '0.');

$number=12345678.090;
echo rtrim ( $number, '0.'); //12345678.09
$number=12345678.900;
echo rtrim ( $number, '0.'); //12345678.9
$number=12345678.901;
echo rtrim ( $number, '0.'); //12345678.901
$number=12345678.000;
echo rtrim ( $number, '0.'); //12345678

the characters included in rtrim character list are zero and the decimal point incase the font is hard to read
one of those cases where you can change a built in function
rtrim is typically used just for whitespace as rtrim($number);

Apologies MaxMumford,
I assume that everybody uses functions I use as often as I use em

[MDGM]

Thanks for all the replies, I can tell that in future ill be referring to all this info, so thanks :)

Iv gone with floatval(), it seems like the simplest and at the moment does exactly what I want.

Thanks again

Max