Zend_Db is ignoring my default field values!

Question

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

vartec · Accepted Answer · 2009-03-19 17:00:18Z

1

Try:

$data['shouldBeEmtpy'] = new Zend_Db_Expr('NULL');

answered Mar 19, 2009 at 17:00

vartec

135k38 gold badges227 silver badges248 bronze badges

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

3 Comments

fistameeny Over a year ago

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?

vartec Over a year ago

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 Over a year ago

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.

fistameeny · Accepted Answer · 2009-03-20 11:21:27Z

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;
    }
}

Collectives™ on Stack Overflow

Zend_Db is ignoring my default field values!

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related