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, Commented Oct 25, 2011 at 5:44
@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
– dinkan, Commented Oct 25, 2011 at 6:26
do a debug($this->data); exit; before $this->Model->save() occurs; is all the data present as it should be? — Ross
– Ross, Commented Oct 25, 2011 at 9:13
@Ross i tried that one too.. $this->data remains unchanged. This change occurs just before running the insert query. — dinkan
– dinkan, Commented Oct 25, 2011 at 9:24

Mark Northrop · Accepted Answer · 2011-10-25 10:12:25Z

2

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.

answered Oct 25, 2011 at 10:12

Mark Northrop

2,6331 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Britc Over a year ago

I am using Cakephp 2.91 and facing the same issue. When I save a float number lie '103.813595', it will save in the mysql as '103.813599'. The table is a float(10,6). How to solve this ?

Collectives™ on Stack Overflow

cakephp model save issue with float values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related