Re: [fw-db] Zend_Db and NULL values

Bill,

Thanks for the reply.  I really appreciate the time you guys put in to, not only developing the framework, but the support and assistance you give to us users, as well.

Your reply gave me some insight into some design flaws in my table schemas.  After further testing, it DOES appear to be working as you mentioned with stricter primary keys.

Thanks again.

Arthur


Bill Karwin wrote:

 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