2

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'
    ));
4

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:

| Longitude   | decimal(10,7)    | NO   |     | NULL    |                |
| Latitude    | decimal(9,7)     | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.