How to properly save decimal data in magento flat table ?
I created table via magento install script:
mysql> describe my_geo_table;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| entity_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
...
| Longitude | decimal(10,0) | NO | | NULL | |
| Latitude | decimal(10,0) | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
in my test script, I try to save model:
$address = Mage::getModel('Mymodule/my_geo_table')
->setData(...)
->setLatitude(53.5586509)
->setLongitude(9.6469548)
->save();
It saves without error, but when i see mysql table contents, these values are differs :
+-----------+-----------+----------+----------+
| entity_id | ... |Longitude | Latitude |
+-----------+-----------+----------+----------+
| 1 | ... | 54 | 10 |
+-----------+-----------+----------+----------+
How to save decimal value in magento table correctly ?
UPDATE
Thanks to Marius, figured out how to correctly add decimal columns for longitude and latitude. Maybe one can find it useful:
$installer->getConnection()
->addColumn('my_geo_table', 'longitude', array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,
'scale' => 7,
'precision' => 10,
'nullable' => false,
'comment' => 'Longitude'
));
$installer->getConnection()
->addColumn('my_geo_table', 'latitude', array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,
'scale' => 7,
'precision' => 9,
'nullable' => false,
'comment' => 'Latitude'
));
Best Answer
your latitude column (and longitude) are of type
decimal(10,0).This means you can save up to 10 digits in it and none of them are decimals.
Since you want to save 7 decimal digits and the max longitude possible is 180 and the max latitude is 90 you should make your columns look like this: