How to print exact sql query in zend framework ?
Solution 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
Solution 2
from >= 2.1.4
echo $select->getSqlString()
Solution 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.
Solution 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)
Solution 5
Now on Zend2:
$select->getSqlString();
Related videos on Youtube
mymotherland
Eager to learn Live as if you were to die tomorrow. Learn as if you were to live forever! Let us continue learning as long as we live... only to add quality to the life we live! ஊக்கமது கைவிடேல் - எப்போதும் முயற்சியைக் கைவிடக்கூடாது.
Updated on June 22, 2020Comments
-
mymotherland almost 4 years
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
-
mymotherland over 12 yearsThanks vascowhite. But i need to know the update mysql query format not a select query format.how to print this "$this->update...."
-
vascowhite over 12 yearsI don't think that SQL gets stored anywhere, it is generated and executed.
-
mymotherland over 12 yearsThanks,I need to print update query in zend framwork.How can i do as like select object.Do we any method to print the sql query for update method in zend
-
Pratik over 8 yearsThanks but only 2nd approach
Zend_Debug::dump($select->query();
worked -
Pratik over 8 yearsHow did you come to know this ?
-
Rajesh Patel over 7 yearsThis is not good solution ..it quotes all your values in wrong format..
-
Murray Wynnes over 5 years@RajeshPatel It should become valid Mysql if you replace " with `
-
Powerriegel about 5 years
Call to undefined method Zend\Db\Sql\Select::__toString()
onZend DB 2.8.2
-
Sonu Bamniya over 4 years
die($select);
even more. -
Vladimir Ch over 4 yearsok, it will auto trigs
__toString();
but i prefervar_dump($select);die;
golden mean !