8 months ago

How to prevent the rounding of decimals when inserting into mysql database

Posted 8 months ago by graemenewable

I have a strange issue whereby each time I insert or update decimals into my database, the decimal values get rounded. For example if I insert 51.4988814, it gets rounded to 51.00000000.

The decimals in question are both latitude and longitude values.

The code I am using to insert into the database is:

$centre = Centre::updateOrCreate(
    ['wordpress_id' => $centre->ID],
        'name' => $centre->post_title, 
        'postcode' => $centre->acf->postcode,
        'lat' => 51.4988814,
        'lng' => -0.1415936

The table migration looks like this:

Schema::create('centres', function (Blueprint $table) {
    $table->decimal('lat', 10, 8);
    $table->decimal('lng', 11, 8);

One attempt to fix this was to edit my php.ini and change the precision= to a higher number (20) and restart the server but this did not rectify the issue.

I have also tried using float instead of decimal in the database.

I have also tried

'lat' => (float)51.4988814,
'lng' => (float)-0.1415936

Inside the updateOrCreate function.

My model is also very barebones (just a protected $fillable property)

I am now out of ideas.

I have added the output of SHOW CREATE TABLES centres

CREATE TABLE `centres` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `wordpress_id` int(10) unsigned NOT NULL,
 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `postcode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `lat` decimal(10,8) NOT NULL,
 `lng` decimal(11,8) NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,

Please sign in or create an account to participate in this conversation.