How to print exact sql query in zend framework?

Question

I have the following code snippet that I took from the 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 request in zend,

 $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 a possible way to print mysql query in zend? kind advice

Answer 1

In the Zend Framework, select objects have a __toString () method.

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

Answer 2

from> = 2.1.4

 echo $select->getSqlString() 

Answer 3

I traveled hundreds of pages, searched a lot, but I did not find any exact solution. Finally it worked for me. No matter where you are in the controller or model. This code worked for me everywhere. 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 work worked for me.

Answer 4

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

Or you can enable the Zend DB FirePHP profiler , which will provide you with all the queries in a neat format in Firebug (even for UPDATE statements).

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

Answer 5

Now on Zend2:

 $select->getSqlString(); 

Display generated SQL from ZendDbSql object

Answer 6

you can print ..

 print_r($select->assemble()); 

Answer 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; 

Answer 8

Use this: -

 echo $select->query(); 

or

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

Answer 9

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

Answer 10

I did it 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(); 

Answer 11

even shorter:

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

and more shorter:

 echo $select .""; die; 

Answer 12

A query returned from the profiler or query object will have placeholders if you use them.

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

This list will list all the requests that have been executed since its inclusion. Remember to disable this once you have collected your sample. On the active server; this journal can fill up very quickly.

From a MySQL terminal or query tool such as MySQL Workbench, run:

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

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

 SELECT * FROM mysql.general_log 

To disable the query log:

 SET GLOBAL general_log = 0; 

To make sure it is turned off:

 SHOW VARIABLES LIKE 'general%'; 

This helped me find a query in which the placeholder was not replaced with Zend DB. I can not see this with the profiler.

Answer 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); 

Answer 14

This is from the Zend Framework documentation (i.e. UPDATE):

 echo $update->getSqlString(); 

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

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

Have a nice day :)