1

I'm using Zend Framework 1.7 with a MySQL 5.0 database for a project I'm doing. Part of the system is a longish form that asks a user for various dates (in dd/mm/yyyy format) as well as some integer fields.

In my MySQL table, all these fields have a default of null. When I come to save the form data with a function in my model (which extends Zend_Db_Table), any blank integers fields are set to 0, and any blank date fields are set to 0000-00-00 - in both cases, they should be null.

I believe this is because Zend_Db_Table->insert() is quoting all values, including blank ones. Is there any way I can alter this behaviour? I'm currently having to loop through groups of fields, setting them to null as appropriate.

Cheers,
Matt

Charles
  • 50,943
  • 13
  • 104
  • 142
fistameeny
  • 1,048
  • 2
  • 14
  • 27

2 Answers2

1

Try:

$data['shouldBeEmtpy'] = new Zend_Db_Expr('NULL');
vartec
  • 131,205
  • 36
  • 218
  • 244
  • Hi, Yeah, I'm doing something similar at the moment. However, I've got about 30 fields that need it doing. I'm looping through them, but it's not very efficient. And, if I change/add any fields, I have to change my code. Any other ways round it? – fistameeny Mar 19 '09 at 17:17
  • Use array_fill_keys($emptyFields, new Zend_Db_Expr('NULL')) or array_map to map whatever your representation of empty field is to Zend_Db_Expr('NULL'). – vartec Mar 19 '09 at 18:45
  • Other option is to create null $data array with array_fill_keys($allFields, new Zend_Db_Expr('NULL')), then you set fields that you want to set, and unset fields stay null. – vartec Mar 19 '09 at 18:48
1

vartec - thanks for your help. Your ideas gave me a good starting point. I've developed it further, extending Zend_Db_Table, adding a function that will suck the column metadata out of MySQL, and use this to set default values. I've posted a rough draft of it below. I haven't made any attempt to simplify it yet, and have only covered the field types I need immediately. Hopefully, it might help others having the same problem.

public function reformatData($array) {
    if (!is_array($array)) {
        return false;
    }

    $cols = $this->info(Zend_Db_Table_Abstract::METADATA);

    if (is_array($cols)) {
        foreach ($cols as $col) {
            if (array_key_exists($col['COLUMN_NAME'], $array)) {
                switch ($col['DATA_TYPE']) {
                    case 'int': case 'tinyint':
                        if ($array[$col['COLUMN_NAME']] == '') {
                            $newArray[$col['COLUMN_NAME']] = new Zend_Db_Expr('null');
                        }
                        else {
                            $newArray[$col['COLUMN_NAME']] = $array[$col['COLUMN_NAME']];
                        }
                        break;

                    case 'date':
                        if ($array[$col['COLUMN_NAME']] == '') {
                            $newArray[$col['COLUMN_NAME']] = new Zend_Db_Expr('null');
                        }
                        elseif(!Zend_Date::isDate($array[$col['COLUMN_NAME']], 'YYYY-MM-dd')) {
                            $date = new Zend_Date($array[$col['COLUMN_NAME']], null, 'en_GB');
                            $newArray[$col['COLUMN_NAME']] = $date->toString('YYYY-MM-dd'); 
                        }
                        else {
                            $newArray[$col['COLUMN_NAME']] = $array[$col['COLUMN_NAME']];
                        }
                        break;

                    case 'datetime':
                        if ($array[$col['COLUMN_NAME']] == '') {
                            $newArray[$col['COLUMN_NAME']] = new Zend_Db_Expr('null');
                        }
                        elseif(!Zend_Date::isDate($array[$col['COLUMN_NAME']], 'YYYY-MM-dd HH:MM')) {
                            $date = new Zend_Date($array[$col['COLUMN_NAME']], null, 'en_GB');
                            $newArray[$col['COLUMN_NAME']] = $date->toString('YYYY-MM-dd HH:MM'); 
                        }
                        else {
                            $newArray[$col['COLUMN_NAME']] = $array[$col['COLUMN_NAME']];
                        }
                        break;

                    default:
                        $newArray[$col['COLUMN_NAME']] = $array[$col['COLUMN_NAME']];
                        break;
                }           
            }
        }
        return $newArray;
    }
    else {
        return false;
    }
}
fistameeny
  • 1,048
  • 2
  • 14
  • 27