cakephp model save issue with float values

Question

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...??

Have you tried turning on CakePHP debugging info and see what the actual insert query is? — Xint0
@Xint0 yeah, the query goes like INSERT INTO places (place_name, latitude, longitude) VALUES ('St Francis De Sales', 42.3815, -71.0667) It already rounds off the value there before inserting in to the table. — dinkan
do a debug($this->data); exit; before $this->Model->save() occurs; is all the data present as it should be? — Ross
@Ross i tried that one too.. $this->data remains unchanged. This change occurs just before running the insert query. — dinkan

mtnorthrop mtnorthrop · Accepted Answer · 2011-10-25T10:12:25

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.

cakephp model save issue with float values

1 Answers