Show SQL query of controller

Question

I am using cakephp v3.x and mysql.

I would like to see the SQL query of a controller. There is no error with this controller and it runs successfully.

public function appGetResult()
{
    $this->autoRender = false;
    $start_date = $this->request->query['start_date'];
    $end_date = $this->request->query['end_date']; 

    $results = $this->MonthlyReports->getResult($start_date, $end_date);

    echo json_encode($results);
}

How to modify the controller code to display the SQL query used in generating the results? This is not for actual production use. More for examination.

May I ask why the negative vote? Please explain so that I can improve on my future questions. THanks. — guagay_wk
– guagay_wk, Commented Nov 10, 2015 at 14:14

Andrius · Accepted Answer · 2015-11-10 10:48:15Z

2

You can turn on query logging on CakePHP3: http://book.cakephp.org/3.0/en/orm/database-basics.html#query-logging

If you're interested in doing more debugging and such, DebugKit does an amazing job.

answered Nov 10, 2015 at 10:48

Andrius

5,94922 silver badges28 bronze badges

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

8 Comments

guagay_wk Over a year ago

Thanks. I am still somewhat at a loss. How does one get the $conn in $conn->logQueries(true);? Where do I run this code?

Andrius Over a year ago

This should do it: $conn = ConnectionManager::get('connection_name');. Insert your connection_name from the config.

guagay_wk Over a year ago

In which file should I run this line $conn = ConnectionManager::get('connection_name');? In the controller?

Andrius Over a year ago

Yes. I would run it just above the query you want to log, before the logQueries(true).

Andrius Over a year ago

Don't forget to add use Cake\Datasource\ConnectionManager; at the top of your controller.

|

guagay_wk · Accepted Answer · 2015-11-10 14:10:52Z

1

I figured out an indirect way for an answer to my own question. This is an indirect way to see the MySQL query statements.

public function appGetResult()
{
    //$this->autoRender = false;
    $start_date = $this->request->query['start_date'];
    $end_date = $this->request->query['end_date']; 

    $results = $this->MonthlyReports->getResult($start_date, $end_date);

    echo json_encode($results);
}

By commenting away $this->autoRender = false; and not having a view .ctp, the php page stops with an error. The DebugKit panel will be shown on the erroneous webpage. From the DebugKit panel, one will be able to see the SQL statements.

answered Nov 10, 2015 at 14:10

guagay_wk

28.3k64 gold badges202 silver badges311 bronze badges

1 Comment

Fury Over a year ago

Cool. how silly way :D. cakephp 3.x it self has it's on debugging tool (called DebigKit). I fyo have it enabled then you would have the debugKit toolbar at the footer of the page. Click "Sql Log" button it shows you all the queries that runs on that time.

Quy Le · Accepted Answer · 2016-03-17 06:48:13Z

0

You can use getLog() or showLog() function to do it

var_dump($this->MonthlyReports->getDataSource()->getLog(false, false));
var_dump($this->MonthlyReports->getDataSource()->showLog());

edited Mar 17, 2016 at 6:48

answered Mar 17, 2016 at 4:08

Quy Le

2,37926 silver badges18 bronze badges

1 Comment

Freefri Over a year ago

I think this answer is about cakephp 2.x and the quiestion is about cakephp 3.x

Collectives™ on Stack Overflow

Show SQL query of controller

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related