How to print exact sql query in zend framework

Question

I have the following piece of code which i taken from model                              select    this- gt  db- gt select                       - gt from  this- gt  name                      - gt where  shipping     type                      - gt where  customer id     userid                    echo   select  exit     which gives exact mysql query                      When i use update query in zend like      up value   array  billing   gt   0     this- gt update  up value  customer id     userid   and address id  lt  gt     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

User · Answer

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.

User · Answer

Use this -  echo  select- gt query      or  Zend Debug  dump  select- gt query

User · Answer

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- gt getProfiler  - gt setEnabled true    profiler    db- gt getProfiler        Execute your any of database query here like select  update  insert   The code below must be after query execution  query     profiler- gt getLastQueryProfile     params    query- gt getQueryParams     querystr     query- gt getQuery     foreach   params as  par         querystr   preg replace                 par         querystr  1     echo  querystr    Finally this thing worked for me

User · Answer

statement    this- gt sql- gt getSqlStringForSqlObject  HERE GOES Zend Db Sql SelectSQL object     echo  SQL statement   statement     Example    select    this- gt sql- gt select         select- gt from array   u    gt   users       select- gt join      select- gt group  u id         statement    this- gt sql- gt getSqlStringForSqlObject  select   echo  statement

User · Answer

Select objects have a   toString   method in Zend Framework   From the Zend Framework manual    select    db- gt select                - gt from  products      sql    select- gt   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- gt getProfiler  - gt setEnabled true       your code  this- gt update  up value  customer id     userid   and address id  lt  gt     data  address Id       Zend Debug  dump  db- gt getProfiler  - gt getLastQueryProfile  - gt getQuery     Zend Debug  dump  db- gt getProfiler  - gt getLastQueryProfile  - gt getQueryParams      db- gt getProfiler  - gt setEnabled false     http   framework zend com manual en zend db select html

User · Answer

This one s from Zend Framework documentation  ie  UPDATE    echo  update- gt getSqlString       Bonus  I use this one in my own model files   echo  this- gt tableGateway- gt getSql  - gt getSqlstringForSqlObject  select     Have a nice day

User · Answer

Now on Zend2     select- gt getSqlString      Displaying the generated SQL from ZendDbSql object

User · Answer

I have done this by this way     sql   new Sql  this- gt adapter            select    sql- gt select             select- gt from  mock paper             select- gt columns array               is section                       select- gt where array  exam id        gt   exam id  level id        gt   level id  - gt limit 1               sqlstring    sql- gt buildSqlString  select           echo  sqlstring          die

User · Answer

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

User · Answer

db- gt getProfiler  - gt setEnabled true       your code      this- gt update  table    data   where       Zend Debug  dump  db- gt getProfiler  - gt getLastQueryProfile  - gt getQuery         Zend Debug  dump  db- gt getProfiler  - gt getLastQueryProfile  - gt getQueryParams          db- gt getProfiler  - gt setEnabled false

User · Answer

You can use Zend Debug  Dump  select- gt 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

User · Answer

you can print    print r  select- gt assemble

User · Answer

even shorter   echo  select- gt   toString     n     and more shorter   echo   select      die

User · Answer

from    2 1 4  echo  select- gt getSqlString

[php] How to print exact sql query in zend framework ?

Examples related to php

Examples related to mysql

Examples related to zend-framework

Examples related to zend-db