Zend_Db_Table: What happens when insert results in an error?

Question

This is probably an easy question to answer, but I don't see it mentioned in the documentation...

What happens when insert results in an error? Is an exception thrown? What type? I'm trying to understand what would happen when trying to insert duplicate data in a column that is unique.

So I feel like I need to do some checking first...

protected function _emailAlreadySubscribed($email)
{
    $select = $this->_model->select()->where('email = ?', $email);
    $rows = $table->fetchAll($select);
    if ($rows->count()) {
        return true;
    }
    return false;
}

Is this the correct way to check?

Answer 1

You probably meant if ($rows->count()). Like this:

protected function _emailAlreadySubscribed($email)
{
    $select = $this->_model->select()->where('email = ?', $email);
    $rows = $table->fetchAll($select);
    if ($rows->count()) {
        return true;
    }
    return false;
}

That's a valid approach. An even more elegant and generic approach is to write a custom validator doing the same thing. In most cases you will aquire email addresses over a form anyways:

class Project_Validate_DbUnique extends Zend_Validate_Abstract
{
    const NOT_UNIQUE = 'dbUniqueNotUnique';

    protected $_messageTemplates = array(
        self::NOT_UNIQUE => "'%column%' '%value' already exists"
    );

    /**
    * @var array
    */
    protected $_messageVariables = array(
        'column'  => '_column',
    );

    /**
     * The table where to check for unique value in column 
     *
     * @var Zend_Db_Table
     */
    protected $_dbTable = NULL;

    /**
     * The column name where to check for unique value 
     *
     * @var string
     */
    protected $_column = '';

    /**
     * The values of the primary key for this row if updating - to exclude the current row from the test 
     *
     * @var array
     */
    protected $_rowPrimaryKey = NULL;

    public function __construct(Zend_Db_Table_Abstract $table, $column, $rowPrimaryKey = NULL)
    {
        $this->_dbTable = $table;
        $this->_column = $column;
        $this->_rowPrimaryKey = $rowPrimaryKey;
    }
    public function isValid($value)
    {
        $this->_setValue($value);

        $select = $this->_dbTable->select();
        $select->where($this->_dbTable->getAdapter()->quoteInto($this->_column . ' = ?', $value));
        if (isset($this->_rowPrimaryKey))
        {
            $rowPrimaryKey = (array) $this->_rowPrimaryKey;
            $info = $this->_dbTable->info();

            foreach ($info['primary'] as $key => $column)
            {
                $select->where($this->_dbTable->getAdapter()->quoteInto($column . ' != ?', $rowPrimaryKey[$key - 1]));                
            }
        }

        $row = $this->_dbTable->fetchAll($select);
        if ($row->count())
        {
            $this->_error();
            return false;
        }

        return true;
    }
}

Answer 2

No, checking to see if the email already exists is not a valid solution. Someone else could insert a duplicate value after your check but before your INSERT. The only solution is to lock the entire table.

When you violate a unique key in Zend_Db_Table::insert(), a Zend_Db_Statement_Exception (or a subclass thereof) is thrown.

See also MySQL Insert: Test first?

Answer 3

For inserts, I make sure my db table has proper indexes (in this case, a unique index on emailAddress) and then I just wrap my insert in a try/catch block.

I let the DB tell me if it's a dupe rather than checking first.

Insert will throw exceptions when the database fails. You can then check why the database failed using the exception and display the appropriate messaging to your user.

Answer 4

I've been playing around with Zend Framework to try and detect DB exceptions and handle them. Unfortunately, once you have caught the exception, the getCode() method always seems to return '0', so I ended up using the following code to check for duplicates:

try { 
 $model->insert($data);
 $added++; 
} catch (Zend_Db_Exception $e) {
 if(strstr($e->getMessage(), '1062 Duplicate')) {
  // duplicate
  $duplicates++;
 } else {
  // general error
  $errors++;
 }
}

Not the most graceful code I know, so if anyone knows how to handle these exceptions better or has an exception code reference please chip in!