Zend Framework 2- Dealing Empty with columns(type float) of empty value

The problem is actually MySQL error:

Incorrect decimal value: '' for column demand_charge at row 1

I have an entity similiarly to ZfcUser:

class DeviceConfiguration
{
    // ......... other properties
    /**
     * @var float
     *
     */
    private $demandCharge;

    // ......... other methods

    /**
     * Set demandCharge
     *
     * @param float $demandCharge
     * @return DeviceConfiguration
     */
    public function setDemandCharge($demandCharge)
    {
        $this->demandCharge = $demandCharge;

        return $this;
    }

    /**
     * Get demandCharge
     *
     * @return float 
     */
    public function getDemandCharge()
    {
        return $this->demandCharge;
    }
}

I have a mapper similiarly to ZfcUser with method to insert data

class DeviceConfigurationMapper extends AbstractDbMapper
{
    public function insert($device, $tableName = null, HydratorInterface $hydrator = null)
    {
        $device->setLastUpdate(new DateTime());
        $result = parent::insert($device);
        $device->setId($result->getGeneratedValue());
        return $result;
    }        
}

I also have a class, DeviceConfigurationHydrator which is just an extension of Zend\Stdlib\Hydrator\ClassMethods to extract data from the entity or to hydrate data to the entity!

The field, demand_charge is of type float in the Mysql database and is not mandatory which I have defined in the InputFilter. So, when the user keeps the field empty, I get an SQL error(as metioned above).

How do I solve this problem?

Answers


DROP TABLE IF EXISTS testfloat;
Query OK, 0 rows affected (0.11 sec)

CREATE TABLE testfloat(id int,prod_name CHAR(10), price DECIMAL);
Query OK, 0 rows affected (0.29 sec)

You are getting message because you are inserting BLANK in DECIMAL field. Example below

INSERT INTO testfloat(id,prod_name,price) values(1,'A','');
Query OK, 1 row affected, 1 warning (0.00 sec)

show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '' for column 'price' at row 1 |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

INSERT INTO testfloat(id,prod_name,price) values(1,'A','ABC');
Query OK, 1 row affected, 1 warning (0.01 sec)

show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: 'ABC' for column 'price' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

Insert proper value.You should insert 0.00 when you insert blank

INSERT INTO testfloat(id,prod_name,price) values(1,'A',10.00);
Query OK, 1 row affected (0.00 sec)

SELECT * FROM testfloat;
+------+-----------+-------+
| id   | prod_name | price |
+------+-----------+-------+
|    1 | A         |     0 |
|    1 | A         |     0 |
|    1 | A         |    10 |
+------+-----------+-------+
3 rows in set (0.00 sec)

The answer I found till now is pretty simple! I added the following to the DeviceConfigurationHydrator hydrator.

class DeviceConfigurationHydrator extends ClassMethods
{
    public function extract($object)
    {

        $data = parent::extract($object);
        $field = 'demand_charge';
        if (isset($data[$field]) && empty($data[$field])) {
                unset($data[$field]);
        } 

        return $data;
    }
}

Even it's quite late - but if someone will get to the same problem

To solve this problem you can add the below filter for your demand_charge property.

In Zend Framework 2: Zend_Filter_Null In Zend Framework 3: ToNull

Internally the framework changes the empty string '' to 0 so your database query does not fail.


Need Your Help

How to run executable program from a trigger?

triggers odbc exe advantage-database-server

How would I create a trigger to execute a .exe file on UPDATE or INSERT for a table in Advantage Database Server?

Jqwidgets rendergridrows

jquery widget

please can anyone help me what is rendergridrows supposed to achieve?