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

135479 次浏览
小开

Use this:-

echo $select->query();

or

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

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)

小开
最佳答案

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

小开

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.

小开

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.

小开

you can print..

print_r($select->assemble());
小开

from >= 2.1.4

echo $select->getSqlString()
小开 
$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;
小开 
$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);
小开

Now on Zend2: 

$select->getSqlString();

Displaying the generated SQL from ZendDbSql object

小开

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();
小开

even shorter:

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

and more shorter:

echo  $select .""; die;
小开

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 :)

小开

The query returned from the profiler or query object will have placeholders if you're using those.

To see the exact query run by mysql you can use the general query log.

This will list all the queries which have run since it was enabled. Don't forget to disable this once you've collected your sample. On an active server; this log can fill up very fast.

From a mysql terminal or query tool like MySQL Workbench run:

SET GLOBAL log_output = 'table';
SET GLOBAL general_log = 1;

then run your query. The results are stored in the "mysql.general_log" table.

SELECT * FROM mysql.general_log

To disable the query log:

SET GLOBAL general_log = 0;

To verify it's turned off:

SHOW VARIABLES LIKE 'general%';

This helped me locate a query where the placeholder wasn't being replaced by zend db. Couldn't see that with the profiler.


提交答案