0

Good morning!

Hi, i have problem to show data value from "Medium Text" field in MySQL.

data in my database: 119.511865

result on php: 119.51186499999999

How to show data on database same with result on php?

[UPDATE] here my data on field coordinates:

119.5206,-5.071414,0.0 119.52059600000001,-5.071347,0.0 119.52062600000001,-5.071106000000001,0.0 119.520632,-5.071052,0.0 119.520711,-5.070732,0.0 119.52071299999999,-5.070723,0.0 119.52271800000001,-5.070708,0.0 119.523891,-5.070702,0.0 119.523942,-5.070702000000001,0.0 119.524918,-5.0707,0.0 119.525947,-5.070687,0.0 119.52701800000001,-5.070678,0.0 119.52807499999999,-5.070671999999999,0.0 119.531112,-5.0706,0.0 119.531166,-5.070577,0.0 119.53105900000001,-5.070378999999999,0.0 119.53094500000002,-5.070183,0.0 119.53083,-5.069989,0.0 119.530716,-5.069795,0.0 119.53059400000001,-5.069605,0.0 119.530472,-5.069418,0.0 119.530342,-5.069234999999999,0.0 119.530205,-5.0690550000000005,0.0 119.53006,-5.068882,0.0 119.52990700000001,-5.068721999999999,0.0 119.52973200000001,-5.068572,0.0 119.529556,-5.068433,0.0 119.529373,-5.068300999999999,0.0 119.52919,-5.068173,0.0 119.529007,-5.068047,0.0 119.528824,-5.067916,0.0 119.528641,-5.067781,0.0 119.52846500000001,-5.067639,0.0 119.528305,-5.067486,0.0 119.52816,-5.067315,0.0 119.52812500000002,-5.067265,0.0 119.5281,-5.067196000000001,0.0 119.52803500000002,-5.066981,0.0 119.528013,-5.066659,0.0 119.52799000000002,-5.066337,0.0 119.528031,-5.066015,0.0 119.527955,-5.065704000000001,0.0 119.52789,-5.065403,0.0 119.527803,-5.065081,0.0 119.52769400000001,-5.064759,0.0 119.527608,-5.064686,0.0 119.52757999999999,-5.064484000000001,0.0 119.527317,-5.064455999999999,0.0 119.527007,-5.064268,0.0 119.52668499999999,-5.064097,0.0 119.526278,-5.06397,0.0 119.525677,-5.063908,0.0 119.52537800000002,-5.063952,0.0 119.52509900000001,-5.064017999999999,0.0 119.52477899999998,-5.064126000000001,0.0 119.52452099999998,-5.064127,0.0 119.524093,-5.064129000000001,0.0 119.52279600000001,-5.063951,0.0 119.52249600000002,-5.063844999999999,0.0 119.522153,-5.0637609999999995,0.0 119.521467,-5.063742000000001,0.0 119.52110300000001,-5.063743,0.0 119.520417,-5.063746,0.0 119.51988199999998,-5.063769,0.0 119.51951799999999,-5.063728,0.0 119.519003,-5.063623,0.0 119.518574,-5.063431,0.0 119.51808000000001,-5.063218000000001,0.0 119.517651,-5.063069,0.0 119.517351,-5.063006000000001,0.0 119.51711599999999,-5.063028,0.0 119.51677300000001,-5.063137,0.0 119.51643100000001,-5.063224,0.0 119.51603400000002,-5.063108,0.0 119.51564800000001,-5.063066,0.0 119.515348,-5.062939,0.0 119.51502599999999,-5.062875,0.0 119.51453400000001,-5.062920000000001,0.0 119.514106,-5.063029,0.0 119.513571,-5.063203000000001,0.0 119.51329300000002,-5.063462,0.0 119.513037,-5.063764000000001,0.0 119.512824,-5.0640220000000005,0.0 119.512697,-5.064366999999999,0.0 119.512613,-5.064732,0.0 119.512593,-5.065205000000001,0.0 119.51257300000002,-5.065699,0.0 119.512554,-5.066279,0.0 119.512299,-5.066796,0.0 119.51217100000001,-5.066968000000001,0.0 119.51197900000001,-5.067162000000001,0.0 119.51163700000001,-5.067314,0.0 119.511209,-5.067487,0.0 119.510888,-5.067552999999999,0.0 119.51046000000001,-5.067727,0.0 119.50984,-5.067900999999999,0.0 119.508311,-5.068541,0.0 119.50784,-5.068714,0.0 119.507567,-5.068777999999999,0.0 119.507431,-5.068737,0.0 119.507287,-5.068689,0.0 119.50715399999999,-5.0687169999999995,0.0 119.506747,-5.068675000000001,0.0 119.50642500000001,-5.068591,0.0 119.506168,-5.068440999999999,0.0 119.50606000000002,-5.068263,0.0 119.506032,-5.068256,0.0 119.505909,-5.068034,0.0 119.50580200000002,-5.06797,0.0 119.505566,-5.067821,0.0 119.50533,-5.067714,0.0 119.50481500000001,-5.067652000000001,0.0 119.504408,-5.067610000000001,0.0 119.50387200000002,-5.067548,0.0 119.503486,-5.06755,0.0 119.503187,-5.067572,0.0 119.50295099999998,-5.067659,0.0 119.50287900000001,-5.067718,0.0 119.50273700000001,-5.067746,0.0 119.502438,-5.067853999999999,0.0 119.502139,-5.06807,0.0 119.50162600000002,-5.068309,0.0 119.50115500000001,-5.068547,0.0 119.500663,-5.068785000000001,0.0 119.50029999999998,-5.068894,0.0 119.49988200000001,-5.068948999999999,0.0 119.499196,-5.068823,0.0 119.498703,-5.068653000000001,0.0 119.49838100000001,-5.068547,0.0 119.49777999999999,-5.0683560000000005,0.0 119.497451,-5.0682100000000005,0.0 119.497458,-5.06828,0.0 119.497491,-5.06875,0.0 119.49746200000001,-5.069502,0.0 119.49740200000001,-5.069973,0.0 119.49734100000002,-5.070474000000001,0.0 119.49734199999999,-5.070725,0.0 119.497343,-5.071039,0.0 119.49731400000002,-5.071445999999999,0.0 119.49730000000001,-5.071916,0.0 119.497394,-5.072104000000001,0.0 119.49755200000001,-5.072386,0.0 119.49767800000001,-5.072698999999999,0.0 119.497866,-5.072792,0.0 119.498116,-5.072854000000001,0.0 119.498304,-5.072947,0.0 119.49855500000001,-5.073197,0.0 119.49877499999998,-5.073541,0.0 119.49883900000002,-5.073822,0.0 119.49892199999998,-5.074071,0.0 119.498934,-5.074103999999999,0.0 119.498935,-5.074355,0.0 119.498905,-5.074699999999999,0.0 119.49881300000001,-5.07517,0.0 119.498845,-5.075546,0.0 119.49894,-5.075797,0.0 119.499066,-5.076016,0.0 119.499318,-5.076453000000001,0.0 119.499349,-5.076547,0.0 119.499319,-5.076892000000001,0.0 119.49938699999998,-5.078271,0.0 119.499391,-5.079210999999999,0.0 119.499298,-5.079463,0.0 119.499361,-5.079588000000001,0.0 119.49958100000002,-5.079869000000001,0.0 119.499801,-5.080244,0.0 119.49989600000002,-5.080432,0.0 119.49995900000002,-5.080714,0.0 119.50002300000001,-5.080902,0.0 119.50002300000001,-5.080993999999999,0.0 119.502111,-5.081472,0.0 119.503832,-5.082776,0.0 119.50511400000002,-5.083742,0.0 119.505703,-5.084187,0.0 119.505715,-5.084095,0.0 119.506043,-5.083532999999999,0.0 119.506293,-5.083207999999999,0.0 119.507215,-5.081518,0.0 119.50750800000002,-5.081272,0.0 119.50835799999999,-5.07946,0.0 119.50969699999999,-5.077704,0.0 119.50996499999998,-5.077447,0.0 119.510361,-5.077155,0.0 119.51161899999998,-5.076447000000001,0.0 119.51244,-5.07604,0.0 119.513844,-5.075267,0.0 119.51507499999998,-5.074615,0.0 119.516886,-5.073601,0.0 119.51747300000001,-5.073225,0.0 119.51961999999999,-5.071982,0.0 119.5206,-5.071414,0.0

Community
  • 1
  • 1
Muh Ghazali Akbar
  • 1,169
  • 3
  • 13
  • 21
  • Hello. What is the type of the field you tried to display ? float ? decimal ? how many digits ? And what is the request ? (any function as SUM/AVG/... used ?) – Georges O. Dec 21 '16 at 21:47
  • i use Medium Text on my field, exactly i store polygon data in this field. – Muh Ghazali Akbar Dec 21 '16 at 21:49
  • 1
    I think it's not obvious from what you've shown here how the medium text in your database is getting converted to a float in PHP. – Don't Panic Dec 21 '16 at 21:49
  • just need to show the original value from database – Muh Ghazali Akbar Dec 21 '16 at 21:50
  • Possible duplicate of [Convert float to string in php?](http://stackoverflow.com/questions/6876666/convert-float-to-string-in-php) – zod Dec 21 '16 at 21:51
  • Please, provide the SQL request used to display the data :) If you execute the request on phpmyadmin directly (for example) ... this is the same result as on your source code? – Georges O. Dec 21 '16 at 21:51
  • You can't, this is a memory limitation in all languages. Floating-point inaccuracy errors usually arise with floating-point arithmetic – OverCoder Dec 21 '16 at 21:51
  • PHP auto convert to float? – Muh Ghazali Akbar Dec 21 '16 at 21:52
  • Right, well, some things are obviously happening in between that we can't see here. Can you provide a [mcve]? – Don't Panic Dec 21 '16 at 21:52
  • @OverCoder have solution sir? – Muh Ghazali Akbar Dec 21 '16 at 21:55
  • Can you show us the code you've tried? – Wolverine Dec 21 '16 at 21:57
  • I was asking about PHP code too. – Wolverine Dec 21 '16 at 21:59
  • @Perumal93 i just use select query from laravel. – Muh Ghazali Akbar Dec 21 '16 at 22:00
  • @MuhGhazaliAkbar What is the library you're using to interact with your SQL server? You need to find an option or something so it doesn't automatically convert float strings to actual floats. But, why are you using float numbers? Float numbers can never be actually accurate. Why are you not using `FLOAT` anyways? – OverCoder Dec 21 '16 at 22:01
  • @OverCoder If floats are not accurate, what else should be used? Like `double` or what? What way do you mean that? – Wolverine Dec 21 '16 at 22:03
  • @MuhGhazaliAkbar Each row in the database contains data like you posted in the question? or is it just the whole data from all the rows you posted? – Wolverine Dec 21 '16 at 22:06
  • @OverCoder what the data type of latitude and longtitude polygon? float? i use medium text – Muh Ghazali Akbar Dec 21 '16 at 22:06
  • @OverCoder it just one row – Muh Ghazali Akbar Dec 21 '16 at 22:07
  • One row has all those data that you posted in the question? – Wolverine Dec 21 '16 at 22:08
  • @MuhGhazaliAkbar I'm not entirely sure, `double` offers more accuracy up to something like 16 fractions, but you shouldn't rely on float/double accuracy for critical applications, especially when dealing with money. For dealing with Lon/Lat, I don't think the difference you're seeing is actually important, you can just reformat that number to limit the number of fractions up to 6 or something when output-ing, and you should be fine – OverCoder Dec 21 '16 at 22:08
  • @OverCoder What else would you suggest over `float` and `double`? – Wolverine Dec 21 '16 at 22:11
  • Interesting article for longitude/latitude mysql storage: http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql : **Use MySQL's spatial extensions with GIS.** You have also some reflections with standard types – Georges O. Dec 21 '16 at 22:15
  • @Perumal93 [This](https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html) mentions `DOUBLE PRECISION(M, D)`, which I *think* would work – OverCoder Dec 21 '16 at 22:23
  • @Perumal93 [Look at this](http://stackoverflow.com/a/11086776/2164304) – OverCoder Dec 21 '16 at 22:24

1 Answers1

1

I tested on my side.

Via PhpMyAdmin / SQL

My request:

 SELECT id, value, AVG(value), SUM(value), value*1, value+1 FROM `41272408` GROUP BY id

The result:

Structure

Result image

It's working well :)

Via PHP

My code:

<?php

$sql = 'SELECT id, value, AVG(value), SUM(value), value*1, value+1 FROM `41272408` GROUP BY id';

$sth = $dbh->prepare($sql);
$sth->execute();

foreach( $sth->fetchAll(PDO::FETCH_ASSOC) as $row ) {
    echo implode(' ; ', $row);
    echo '<br />';

    echo 'with +1: ';
    echo implode(' ; ', array_map(function($v) { return $v + 1; }, $row));
    echo '<hr />';
}

And the output:

1 ; 119.511865 ; 119.511865 ; 119.511865 ; 119.511865 ; 120.511865
with +1: 2 ; 120.511865 ; 120.511865 ; 120.511865 ; 120.511865 ; 121.5118652 ;
---
119.51186499999999 ; 119.51186499999999 ; 119.51186499999999 ; 119.51186499999999 ; 120.51186499999999
with +1: 3 ; 120.511865 ; 120.511865 ; 120.511865 ; 120.511865 ; 121.511865

As you can see, it's working well ... except for a PHP arithmetic operation (+1).

Refers to PHP's documentation about float to find mode information: http://php.net/manual/fr/language.types.float.php

Extract:

Floating point numbers have limited precision. [...] Non elementary arithmetic operations may give larger errors, and, of course, error propagation must be considered when several operations are compounded.

So never trust floating number results to the last digit, and do not compare floating point numbers directly for equality. If higher precision is necessary, the arbitrary precision math functions and gmp functions are available.

So, follow the documentation :) Use specific library for operations

Other points

Interesting article for longitude/latitude MySql storage: What is the ideal data type to use when storing latitude / longitudes in a MySQL database? Use MySQL's spatial extensions with GIS. You have also some reflections with standard types

Community
  • 1
  • 1
Georges O.
  • 972
  • 1
  • 9
  • 21