PHP, MySQL and the Story of the Missing Decimals

Marcus Olsson
Jul 20, 2017 · 2 min read

I’m running a site (built on Laravel) which uses Google’s Javascript API for Google Maps. This how it is supposed to look:

Just a simple map with markers placed on cities. To achieve this I use the cities latitude and longitude parsed via the google.maps.LatLng-class.

However, after a minor update, this is what my map looked like:

Somehow the markers ended up on neat little rows and columns equally distanced from each other, and in the wrong places (obviously).

After some quick debugging I discovered that the decimals for the longitudes and latitudes had went missing — how odd!

The issue with setlocale() and float

The previous update which seemed to cause all this was a simple installation of a language pack on Ubuntu. All I wanted was to use PHP’s strftime()-function to enable formatting to Swedish.

So installing the language pack

$ sudo apt-get install language-pack-sv
$ sudo service php7.1-fpm restart

And in PHP, setting the locale

setlocale(LC_ALL, ‘sv_SE.utf8’);

Apparently, somehow this did not jive well with float-numbers directly from MySQL — for instance, Stockholm have the lat/long of 59.33, 18.06 while after the update these was printed out as simply 59, 18.

For all of us who is non-native English users, this kind of makes sense as we in Sweden (and many other locations around the world) use a comma instead of a “dot” for decimal separation. This somehow confuses PHP when it reads the MySQL table, i.e.: float(8,2) — PHP simply removes everything after the dot upon localization.

The solution

The solution is quite simple; and there are two of them that are quite quick to implement.

First of, we could just convert the field to a decimal field instead of a float field; in Laravel I just ran a migration like this:

Schema::table(‘cities’, function(Blueprint $table) {
$table->decimal(‘lat’, 8, 2)->change();
$table->decimal(‘long’, 8, 2)->change();
});

PHP seems to cope with this a lot better. But, if it is only date/time-functions (probably most commonly strftime()) we are looking to localize, then this might be a better way to go:

setlocale(LC_TIME, ‘sv_SE.utf8’);

As simple as that; then PHP won’t try to handle and localize numeric values.

Using PHP’s number_format-function might also be an option, this however might cause some unexpected issues elsewhere.

That’s it for now. If this helps anyone out, feel free to reach out on Twitter or via my website.

)
Marcus Olsson

Written by

Freelancing web developer building awesome stuff for the interwebs. https://marcusolsson.me

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade