CODEWITHSUNDEEP
mysqlcakephpfloating-point
dinkan
dinkan

Reputation: 83

cakephp model save issue with float values

I've a table named 'Places' to save information about places

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| place_name       | varchar(120) | NO   |     | NULL    |                |
| latitude         | float(10,6)  | NO   |     | NULL    |                |
| longitude        | float(10,6)  | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

Now when i try to insert values in to the table via a Model in cakephp, say like 

[Place] => Array
        (
            [place_name] => St Francis De Sales
            [latitude] => 42.381486
            [longitude] => -71.066718
        )

The query being executed from cakephp rounds off the value to 4 fractions and I dont want that to happen.. 

INSERT INTO `places` 
(`place_name`, `latitude`, `longitude`) 
VALUES ('St Francis De Sales', 42.3815, -71.0667)

When i check the table, the latitude and longitude values are rounded off or changed..

+-------------------------------+-----------+------------+
| place_name                    | latitude  | longitude  |
+-------------------------------+-----------+------------+
| Saint Francis De Sales Church | 42.379501 | -71.062798 |
+-------------------------------+-----------+------------+

I have no problem in inserting values directly from mysql console. So, i guess this is an issue related with cakephp. How can i solve this...??

Upvotes: 1

Views: 1419

Answers (1)

Mark Northrop
Mark Northrop

Reputation: 2623

The problem lies in the way CakePHP uses sprintf to prepare the insert query.

Read through these bug reports for the details:

http://cakephp.lighthouseapp.com/projects/42648/tickets/2049-losing-accuracy-when-saving-a-decimal

http://cakephp.lighthouseapp.com/projects/42648/tickets/2069-precision-loss-when-saving-floats

The good news is that this bug has apparently been fixed in version 1.3.13 (the latest version of CakePHP 1.3, released on 15 October, 2011).

So you should be able to solve your problem by simply upgrading to 1.3.13.

If upgrading isn't an option for whatever reason, you could also consider whether storing the value as a float is really necessary. If you don't have to any special calculations on the lat/long values and you don't need to sort the records by lat/long, you might be able to store the values as varchar instead.

Upvotes: 2

Related Questions