Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

graemem1995's avatar

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

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
0 likes
6 replies
fylzero's avatar

@graemenewable I would not use type-coercion at all. That could be causing your rounding issue. I don't think your data type is the problem but I would use $table->double('amount', 12, 7);. I know lat/lng numbers can get to the 100s, not sure if the 1000s. It looks like you want to keep a scale of 7 digits. So use that and just store the number without (float) or anything in front of the value.

If the value still rounds after this... hardcode the numbers you are storing and see if they round. If they don't you have a problem somewhere else in your code.

1 like
ejobity's avatar

This solution worked. However i used decimal.

$table->decimal('received_amount',7,7);

Making sure M >= D else you will get an error. $table->decimal('received_amount',M,D);

Snapey's avatar

personally, I always store coordinates as strings and then cast them later if they need to be involved in math

graemem1995's avatar

I changed the type to a double and that made no difference.

I also inserted the value straight into the database via phpMyAdmin and it worked fine.

The strange thing is the project is quite new to I struggle to see anything being wrong with the code. I'll keep investigating.

Geoff38's avatar

graemenewable, what was the solution in the end? We are encountering the exact same issue! No errors, no crashes, Laravel's query logger records an SQL prepared statement containing the right values, but the record in the database has integers in the decimal fields, and the MariaDB query log shows the database received them as integers in the INSERT statement.

I've tried the same things as you, moved from Laravel 5.5.49 to 5.5.48 because on a different server that works fine with the same database. The only difference between the working and non-working setups is PHP7.1 works and PHP7.4 has the issue. The issue is happening somewhere between Laravel's query being built and whatever is sending it to the database.

fulufhelo's avatar

If anyone is still interested in this i had a similar problem and what worked for me is casting numbers to string before i save them

Please or to participate in this conversation.