Re: [fw-db] Zend_Db and NULL values

Arthur Kang wrote:
>
> In a nutshell, I'm looking to avoid:
> if ($search == null) {
> $select->where('column IS NULL')
> }
> else {
> $select->where($db->quoteInto('column = ?', $search))
> }
> Any way to avoid this?
>

Yes: declare your primary key columns as NOT NULL.

Standard SQL semantics are that comparing a column equal to NULL is always
"unknown" (which is effectively false). In other words, column1 = NULL
always fails. You must use the IS NULL predicate to return true if the
column is null.

It's awkward to allow NULLs in your primary key column(s). In fact, in many
SQL databases, and in the SQL standard, declaring a primary key over
nullable column(s) is illegal. You don't actually have a primary key
constraint declared in your example table; you're declaring it only in your
Zend_Db_Table class. So you have no assurance that the database contains
non-null or even unique values. I suggest you use a primary key constraint:

create table testtable (
column1 text not null default 'defaultvalue1',
column2 text not null default 'defaultvalue2',
primary key (column1, column2)
);

Also, I'm not sure what brand of RDBMS you're using, but in many brands, the
TEXT data type can't be part of a primary key, nor can it have a DEFAULT
clause.

Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/Zend_Db-and-NULL-values-tp19097510p19099772.html
Sent from the Zend DB mailing list archive at Nabble.com.