Deleting from a table results in a Cardinality violation

Question

I have a method that deletes a row form a database using the sql adapter provided the the zend framework.

class Table extends Zend_Db_Table_Abstract {

    ...

    $where = $this->getAdapter()->quoteInto(
        'ModID=? AND URL=?',
        array((int)$mid, $url->toString())
    );
    $this->delete($where);

The issue is that this code gives the error:

Zend_Db_Statement_Exception: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

Similar code that specifies only one column in the where clause works fine.

Is there an alternative method to delete rows based on the values of multiple columns?

Answer 1

You could do something like this to utilize already built functionality of Zend_Db_Select:

$select = $this->select();
               ->where('modID = ?', (int)$mid);
               ->where('URL = ?', $url->toString());

$where = $select->getPart(Zend_Db_Select::WHERE);

$this->delete($where);

Some things to note about this solution:

• (Pro) Takes advantage of the already existing functionality of quoting in Zend_Db_Select

• (Pro) Takes advantage of that Zend_Db_Table::delete proxies to the adapter method, so you can provide an array of SQL-parts. From the documentation:

  "Since the table delete() method proxies to the database adapter delete() method, the argument can also be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator."

• (Con) Creates a Zend_Db_Select object, but you are really only interested in the where-clause

Even better would be if the delete method would accept a Zend_Db_Select OR perhaps a Zend_Db_Constraint (non-existing class). You could of course make this a Utility-method so that it is available to all your tables, making deletion easier.

Another method is to simply skip the select object:

$where = "{$this->getAdapter()->quoteInto('modID = ?', (int)$mid)} AND 
          {$this->getAdapter()->quoteInto('URL = ?', $url->toString)}";