Zend DB Framework examine query for an update

Question

So you can use something like this:

$query = $db->select();
$query->from('pages', array('url'));
echo $query->__toString();

to examine the sql that the Zend Db Framework is going to use for that SELECT query. Is there an equivilent way to view the SQL for an update?

$data = array(
   'content'      => stripslashes(htmlspecialchars_decode($content))
);      
$n = $db->update('pages', $data, "url = '".$content."'");
??

Overtrump

unrelated to solution but __toString() is automatically called when an object is cast to a string, so echo $query; is easier to type – Fissirostral

Answer 1

Use Zend_Db_Profiler to capture and report SQL statements:

$db->getProfiler()->setEnabled(true);
$db->update( ... );
print $db->getProfiler()->getLastQueryProfile()->getQuery();
print_r($db->getProfiler()->getLastQueryProfile()->getQueryParams());
$db->getProfiler()->setEnabled(false);

Remember to turn the profiler off if you don't need it! I talked to one fellow who thought he had a memory leak, but it was the profiler instantiating a few PHP objects for each of the millions of SQL queries he was running.

PS: You should use quoteInto() in that query:

$n = $db->update('pages', $data, $db->quoteInto("url = ?", $content));

Agent

Thanks, that did it. Pointing me to the profiler helped me in other areas as well. – Overtrump

I only keep it turned on in dev. – Ancalin

Can the "where" portion of the update() function take an array? Such as $db->update('pages', $data, array("url = ?" => $content, "date > ?" => $date); – Dome

@caligoanimus: I am honestly not sure, I have lost track of the ZF project. Best to ask on the ZF mailing list or read the code yourself. – Agent

Answer 2

No, not directly, since Zend Framework builds and executes the SQL inside the adapter method Zend_Db_Adapter_Abstract::update:

/**
 * Updates table rows with specified data based on a WHERE clause.
 *
 * @param  mixed        $table The table to update.
 * @param  array        $bind  Column-value pairs.
 * @param  mixed        $where UPDATE WHERE clause(s).
 * @return int          The number of affected rows.
 */
public function update($table, array $bind, $where = '')
{
    /**
     * Build "col = ?" pairs for the statement,
     * except for Zend_Db_Expr which is treated literally.
     */
    $set = array();
    foreach ($bind as $col => $val) {
        if ($val instanceof Zend_Db_Expr) {
            $val = $val->__toString();
            unset($bind[$col]);
        } else {
            $val = '?';
        }
        $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
    }

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

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

    /**
     * Execute the statement and return the number of affected rows
     */
    $stmt = $this->query($sql, array_values($bind));
    $result = $stmt->rowCount();
    return $result;
}

You can, temporarily, insert a var_dump and exit inside this method to inspect the sql to ensure that it is correct:

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

Smasher

Answer 3

I quess another way is to log the actual SQL query, rather than changing the ZF library code, by combining the profiler data.

$db->getProfiler()->setEnabled(true);

$db->update( ... );

$query = $db->getProfiler()->getLastQueryProfile()->getQuery();

$queryParams = $db->getProfiler()->getLastQueryProfile()->getQueryParams();

$logger->log('SQL: ' . $db->quoteInto($query, $queryParams), Zend_Log::DEBUG);

$db->getProfiler()->setEnabled(false);

Wildebeest

Have you tried this? It doesn't appear to work in ZF 1.9.5. When you give an array to quoteInto(), it joins it to a comma-separated string, and substitutes the result for each parameter placeholder. Also quoteInto() is not very smart about question marks inside string literals and it doesn't support named parameter placeholders at all. – Agent

No. I didn't try it was a wild guess based on the Zend_Db_Profiler and Zend_Db API. Thanks for verifying ;D – Wildebeest

Answer 4

Recently came across this looking for a way to debug a zend_db_statement. If anyone else comes across this with the same search, you can use the following function.

Just replace "self::getDefaultAdapter()" with your method of getting a DB connection or adapter.

/**
 * replace any named parameters with placeholders
 * @param string $sql sql string with placeholders, e.g. :theKey
 * @param array $bind array keyed on placeholders, e.g. array('theKey', 'THEVALUE')
 * 
 * @return String sql statement with the placeholders replaced
 */
public static function debugNamedParamsSql($sql, array $bind) {
    $sqlDebug = $sql;
    foreach($bind as $needle => $replace) {
        $sqlDebug = str_replace( 
                                ':' . $needle, 
                                self::getDefaultAdapter()->quote($replace), 
                                $sqlDebug
        );
    }        
    return $sqlDebug;        
}

Hairstreak