How to print exact sql query in zend framework ?

问题:

I have the following piece of code which i taken from model,

    ...
                  $select = $this->_db->select()
                    ->from($this->_name)
                    ->where('shipping=?',$type)
                    ->where('customer_id=?',$userid);
                 echo  $select; exit; // which gives exact mysql query.
            .....

When i use update query in zend like ,

$up_value = array('billing'=> '0');
$this->update($up_value,'customer_id ='.$userid.' and address_id <> '.$data['address_Id']);      

Here i want to know the exact mysql query. Is there any possible way to print the mysql query in zend ? kindly advice

回答1:

Select objects have a __toString() method in Zend Framework.

From the Zend Framework manual:

$select = $db->select()
             ->from('products');

$sql = $select->__toString();
echo "$sql\n";

// The output is the string:
//   SELECT * FROM "products"

An alternative solution would be to use the Zend_Db_Profiler. i.e.

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

// your code
$this->update($up_value,'customer_id ='.$userid.' and address_id <> '.$data['address_Id']); 

Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQuery());
Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQueryParams());
$db->getProfiler()->setEnabled(false);

http://framework.zend.com/manual/en/zend.db.select.html

回答2:

from >= 2.1.4

echo $select->getSqlString()

回答3:

I have traversed hundred of pages, googled a lot but i have not found any exact solution. Finally this worked for me. Irrespective where you are in either controller or model. This code worked for me every where. Just use this

//Before executing your query
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$db->getProfiler()->setEnabled(true);
$profiler = $db->getProfiler();

// Execute your any of database query here like select, update, insert
//The code below must be after query execution
$query  = $profiler->getLastQueryProfile();
$params = $query->getQueryParams();
$querystr  = $query->getQuery();

foreach ($params as $par) {
    $querystr = preg_replace('/\\?/', "'" . $par . "'", $querystr, 1);
}
echo $querystr;

Finally this thing worked for me.

回答4:

You can use Zend_Debug::Dump($select->assemble()); to get the SQL query.

Or you can enable Zend DB FirePHP profiler which will get you all queries in a neat format in Firebug (even UPDATE statements).

EDIT: Profiling with FirePHP also works also in FF6.0+ (not only in FF3.0 as suggested in link)

回答5:

Now on Zend2:

$select->getSqlString();

Displaying the generated SQL from ZendDbSql object

回答6:

you can print..

print_r($select->assemble());

回答7:

$statement = $this->sql->getSqlStringForSqlObject( HERE GOES Zend\Db\Sql\SelectSQL object );

echo "SQL statement: $statement";

Example:

$select = $this->sql->select();
...
$select->from(array( 'u' => 'users' ));
$select->join(...
$select->group('u.id');
...
$statement = $this->sql->getSqlStringForSqlObject($select);
echo $statement;

回答8:

Use this:-

echo $select->query();

or

Zend_Debug::dump($select->query();

回答9:

Check out the Zend_Db_Profiler. This allows you to log any SQL statement as it is prepared and executed. It works for UPDATE statements as well as SELECT queries.

回答10:

I have done this by this way

$sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('mock_paper');
        $select->columns(array(
            'is_section'
        ));
        $select->where(array('exam_id = ?' => $exam_id,'level_id = ?' => $level_id))->limit(1);



        $sqlstring = $sql->buildSqlString($select);
        echo $sqlstring;
        die();

回答11:

even shorter:

echo $select->__toString()."\n";

and more shorter:

echo  $select .""; die;

回答12:

This one's from Zend Framework documentation (ie. UPDATE):

echo $update->getSqlString();

(Bonus) I use this one in my own model files:

echo $this->tableGateway->getSql()->getSqlstringForSqlObject($select);

Have a nice day :)

回答13:

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

// your code    
$this->update('table', $data, $where);    
Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQuery());    
Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQueryParams());    
$db->getProfiler()->setEnabled(false);