graemenewable
8 months ago
1454
4
Eloquent

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->bigIncrements('id');
    $table->integer('wordpress_id')->unsigned();
    $table->string('name');
    $table->string('postcode')->nullable();
    $table->decimal('lat', 10, 8);
    $table->decimal('lng', 11, 8);
    $table->timestamps();
});

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,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

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