Applying a SQL function on Zend_Db_Table_Row::save()

Question

Is it possible, when saving a Zend_Db_Table_Row, to make ZF apply a SQL function on one column?

For example, if $row->save() generates by default this SQL query:

UPDATE table SET field = ? WHERE id = ?;

I would like it to automatically apply the GeomFromText() function on this field:

UPDATE table SET field = GeomFromText(?) WHERE id = ?;

Thanks for any hint on how to do this with Zend_Db!

score 1 · Accepted Answer · answered Jul 17 '11 at 23:58

1

Define a custom update method in your class that inherits from Zend_Db_Table (not from the Zend_Db_Table_Row) and use a Zend_Db_Expr to set the column to the function return value.

See the docs here: http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.extending.insert-update.

answered Jul 17 '11 at 23:58

Patrick Cavanaugh

651
5
11

Thank you, I actually just used `$row->column = new Zend_Db_Expr('GeomFromText("...")')` and it worked fine. The drawback is that I need to manually escape the data though. – BenMorel Jul 18 '11 at 17:26

Erick Martinez · Answer 2 · 2011-07-18T02:06:44.147

I am just guessing but you could try this:

<?php
class MyTable extends Zend_Db_Table_Abstract 
{
  protected $_name = 'my_table';

  public function update(array $data, $where) {
       /**
         * Build "col = ?" pairs for the statement,
         * except for Zend_Db_Expr which is treated literally.
         */
        $set = array();
        $i = 0;
        foreach ($data as $col => $val) {
            if ($val instanceof Zend_Db_Expr) {
                $val = $val->__toString();
                unset($data[$col]);
            } else {
                if ($this->_db->supportsParameters('positional')) {
                    $val = ($col == 'field') ? 'GeomFromText(?)' : '?';
                } else {
                    if ($this->_db->supportsParameters('named')) {
                        unset($data[$col]);
                        $data[':col'.$i] = $val;
                        $val = ($col == 'field') ? 'GeomFromText(:col'.$i.')' : ':col'.$i;
                        $i++;
                    } else {
                        /** @see Zend_Db_Adapter_Exception */
                        require_once 'Zend/Db/Adapter/Exception.php';
                        throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
                    }
                }
            }
            $set[] = $this->_db->quoteIdentifier($col, true) . ' = ' . $val;
        }

        $where = $this->_whereExpr($where);

        /**
         * Build the UPDATE statement
         */
        $sql = "UPDATE "
             . $this->_db->quoteIdentifier($this->_name , true)
             . ' SET ' . implode(', ', $set)
             . (($where) ? " WHERE $where" : '');

        /**
         * Execute the statement and return the number of affected rows
         */
        if ($this->_db->supportsParameters('positional')) {
            $stmt = $this->_db->query($sql, array_values($data));
        } else {
            $stmt = $this->_db->query($sql, $data);
        }
        $result = $stmt->rowCount();
        return $result;
    }

    protected function _whereExpr($where)
    {
        if (empty($where)) {
            return $where;
        }
        if (!is_array($where)) {
            $where = array($where);
        }
        foreach ($where as $cond => &$term) {
            // is $cond an int? (i.e. Not a condition)
            if (is_int($cond)) {
                // $term is the full condition
                if ($term instanceof Zend_Db_Expr) {
                    $term = $term->__toString();
                }
            } else {
                // $cond is the condition with placeholder,
                // and $term is quoted into the condition
                $term = $this->quoteInto($cond, $term);
            }
            $term = '(' . $term . ')';
        }

        $where = implode(' AND ', $where);
        return $where;
    }
}
?>

I would not recommend to just use Zend_Db_Expr since, as you can see from the update method in Zend_Db_Adapter_Abstract (from which I actually took this code), it treats Zend_Db_Expr literally, leaving room for sql injection. — Erick Martinez, Jul 18 '11 at 02:16

Applying a SQL function on Zend_Db_Table_Row::save()

2 Answers2