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

The Unknown Dev · Accepted Answer · 2014-08-23 21:54:33Z

141

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, 2014 at 21:54

The Unknown Dev

3,1384 gold badges30 silver badges46 bronze badges

answered Oct 12, 2011 at 3:30

Mark Basmayor

2,5892 gold badges16 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mymotherland Over a year ago

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

Powerriegel Over a year ago

Call to undefined method Zend\Db\Sql\Select::__toString() on Zend DB 2.8.2

Yvan Over a year ago

Another way to do it instead of using __toString(): just force the object to render as string: echo (string) $select;

Whisher · Accepted Answer · 2013-05-13 12:28:43Z

27

from >= 2.1.4

echo $select->getSqlString()

answered May 13, 2013 at 12:28

Whisher

32.9k34 gold badges129 silver badges204 bronze badges

3 Comments

Pratik Joshi Over a year ago

How did you come to know this ?

Rajesh Patel Over a year ago

This is not good solution ..it quotes all your values in wrong format..

Murray Wynnes Over a year ago

@RajeshPatel It should become valid Mysql if you replace " with `

Rajan Rawal · Accepted Answer · 2012-03-01 09:58:19Z

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 · Accepted Answer · 2011-10-11 09:29:56Z

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, 2011 at 9:29

answered Oct 11, 2011 at 9:08

Christian P

12.3k6 gold badges64 silver badges72 bronze badges

Comments

Walter Caraza · Accepted Answer · 2014-12-26 20:27:07Z

4

Now on Zend2:

$select->getSqlString();

Displaying the generated SQL from ZendDbSql object

answered Dec 26, 2014 at 20:27

Walter Caraza

9211 gold badge10 silver badges19 bronze badges

Comments

AvMishra · Accepted Answer · 2013-02-22 07:27:56Z

2

you can print..

print_r($select->assemble());

answered Feb 22, 2013 at 7:27

AvMishra

1,4253 gold badges16 silver badges25 bronze badges

Comments

Username · Accepted Answer · 2013-11-22 15:48:17Z

1

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

answered Nov 22, 2013 at 15:48

Username

133 bronze badges

Comments

Vladimir Ch · Accepted Answer · 2016-11-25 05:50:22Z

1

even shorter:

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

and more shorter:

echo  $select .""; die;

answered Nov 25, 2016 at 5:50

Vladimir Ch

5561 gold badge15 silver badges28 bronze badges

2 Comments

Sonu Bamniya Over a year ago

die($select); even more.

Vladimir Ch Over a year ago

ok, it will auto trigs __toString(); but i prefer var_dump($select);die; golden mean !

KevDev · Accepted Answer · 2019-01-10 04:50:16Z

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, 2019 at 4:50

answered Jan 10, 2019 at 4:42

KevDev

594 bronze badges

Comments

vascowhite · Accepted Answer · 2011-10-11 09:06:51Z

0

Use this:-

echo $select->query();

or

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

answered Oct 11, 2011 at 9:06

vascowhite

18.5k9 gold badges64 silver badges79 bronze badges

3 Comments

mymotherland Over a year ago

Thanks vascowhite. But i need to know the update mysql query format not a select query format.how to print this "$this->update...."

vascowhite Over a year ago

I don't think that SQL gets stored anywhere, it is generated and executed.

Pratik Joshi Over a year ago

Thanks but only 2nd approach Zend_Debug::dump($select->query(); worked

Bill Karwin · Accepted Answer · 2011-10-12 20:12:32Z

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, 2011 at 20:12

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Comments

Rajesh Patel · Accepted Answer · 2016-09-28 10:02:58Z

0

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

answered Sep 28, 2016 at 10:02

Rajesh Patel

2,0461 gold badge17 silver badges22 bronze badges

Comments

txyoji · Accepted Answer · 2019-06-21 20:30:14Z

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.

bensiu · Accepted Answer · 2014-02-16 04:04:31Z

-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, 2014 at 4:04

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

answered Feb 6, 2014 at 4:51

dev4092

2,9111 gold badge18 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to print exact sql query in zend framework ?

14 Answers 14

3 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

3 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related