How to print exact sql query in zend framework ?

Question

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

score 138 · Accepted Answer · edited Aug 23 '14 at 21:54

138

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

edited Aug 23 '14 at 21:54

The Unknown Dev

3,039
4
27
39

answered Oct 12 '11 at 03:30

Mark Basmayor

2,529
2
16
14

Thanks,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 – mymotherland Oct 12 '11 at 05:20
`Call to undefined method Zend\Db\Sql\Select::__toString()` on `Zend DB 2.8.2` – Powerriegel Mar 20 '19 at 14:06
Another way to do it instead of using `__toString()`: just force the object to render as string: `echo (string) $select;` – Yvan Nov 22 '22 at 09:02

score 27 · Answer 2 · answered May 13 '13 at 12:28

27

from >= 2.1.4

echo $select->getSqlString()

answered May 13 '13 at 12:28

Whisher

31,320
32
120
201

How did you come to know this ? – Pratik Joshi Nov 20 '15 at 07:20
1

This is not good solution ..it quotes all your values in wrong format.. – Rajesh Patel Sep 28 '16 at 09:06
1

@RajeshPatel It should become valid Mysql if you replace " with ` – Murray Wynnes Jan 08 '19 at 09:46

score 16 · Answer 3 · answered Mar 01 '12 at 09:58

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.

Christian P · Answer 4 · 2011-10-11T09:29:56.177

8

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)

edited Oct 11 '11 at 09:29

answered Oct 11 '11 at 09:08

Christian P

12,032
6
60
71

score 4 · Answer 5 · answered Dec 26 '14 at 20:27

4

Now on Zend2:

$select->getSqlString();

Displaying the generated SQL from ZendDbSql object

answered Dec 26 '14 at 20:27

Walter Caraza

911
1
10
19

score 2 · Answer 6 · answered Feb 22 '13 at 07:27

2

you can print..

print_r($select->assemble());

answered Feb 22 '13 at 07:27

AvMishra

1,365
3
14
25

score 1 · Answer 7 · answered Nov 22 '13 at 15:48

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

score 1 · Answer 8 · answered Nov 25 '16 at 05:50

1

even shorter:

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

and more shorter:

echo  $select .""; die;

answered Nov 25 '16 at 05:50

Vladimir Ch

487
1
12
26

`die($select);` even more. – Sonu Bamniya Nov 20 '19 at 09:48
ok, it will auto trigs `__toString();` but i prefer `var_dump($select);die;` golden mean ! – Vladimir Ch Nov 20 '19 at 18:09

KevDev · Answer 9 · 2019-01-10T04:50:16.807

1

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

edited Jan 10 '19 at 04:50

answered Jan 10 '19 at 04:42

KevDev

59
4

score 0 · Answer 10 · answered Sep 28 '16 at 10:02

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

score 0 · Answer 11 · answered Jun 21 '19 at 20:30

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.

score 0 · Answer 12 · answered Oct 11 '11 at 09:06

0

Use this:-

echo $select->query();

or

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

answered Oct 11 '11 at 09:06

vascowhite

18,120
9
61
77

Thanks vascowhite. But i need to know the update mysql query format not a select query format.how to print this "$this->update...." – mymotherland Oct 11 '11 at 09:09
I don't think that SQL gets stored anywhere, it is generated and executed. – vascowhite Oct 11 '11 at 09:16
Thanks but only 2nd approach `Zend_Debug::dump($select->query();` worked – Pratik Joshi Nov 19 '15 at 07:35

score 0 · Answer 13 · answered Oct 12 '11 at 20:12

0

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.

answered Oct 12 '11 at 20:12

Bill Karwin

538,548
86
673
828

score -1 · Answer 14 · edited Feb 16 '14 at 04:04

-1

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

edited Feb 16 '14 at 04:04

bensiu

24,660
56
77
117

answered Feb 06 '14 at 04:51

dev4092

2,820
1
16
15

How to print exact sql query in zend framework ?

14 Answers14

Linked

Related