Debugging SQL in controller

Question

I am trying to debug my sql but I am having a hard time. I know I can use this:

<?php echo $this->element('sql_dump'); ?>

to dump the sql but this doesnt (or at least I dont know how to use it) work if I am doing an ajax call. Because the page is not reloaded, the dump does not get refreshed. How can I run my command and debug the sql? Here is the code I have in my controller:

public function saveNewPolicy(){
 $this->autoRender = false;
        $policyData = $this->request->data["policyData"];
        $numRows=0;

         $data = array(
            'employee_id' => trim($policyData[0]["employeeId"]),
            'insurancetype_id'=> $policyData[0]["insuranceTypeId"],
            'company' =>  $policyData[0]["companyName"],
            'policynumber' => $policyData[0]["policyNumber"],
            'companyphone' => $policyData[0]["companyPhone"],
            'startdate'=> $policyData[0]["startDate"],
            'enddate'=> $policyData[0]["endDate"],
            'note' => $policyData[0]["notes"]
        );

        try{
            $this->Policy->save($data);
            $numRows =$this->Policy->getAffectedRows();

            if($numRows>0){
                $dataId = $this->Policy->getInsertID();
                $response =json_encode(array(
                    'success' => array(
                        'msg' =>"Successfully Added New Policy.",
                        'newId' => $dataId
                    ),
                ));
                return $response;
            }else{
                throw new Exception("Unspecified Error.  Data Not Save! ");
            }
        }catch (Exception $e){
            return $this->EncodeError($e);
        }

    }

The problem is that if the company field in my array is empty, empty, the insert will fail without any error. I know it has failed, though, because of the numrows variable I use. I know the field accepts nulls in the database. It seems like the only way for me to debug this is to look at what SQL is being sent to MySql. Anyone know how to debug it? I am using CakePhp 2.4

I would temporarily comment out the json encoding on the page you're getting as ajax, and also temporarily turn autoRender back on, so it will display the dump. Then, either directly navigate to that page in the browser, or if that's too much of a pain, when you get the page via ajax, display its contents, either by sticking them on your webpage somewhere (doesn't matter where since this is temporary), or using console.log. Once you've got the database calls worked out, you can put back those temporary changes. — Kai
– Kai, Commented Nov 27, 2013 at 23:15

skywalker · Accepted Answer · 2013-11-27 23:18:31Z

1

I use this approach. I added this method in my AppModel class:

public function getLastQuery() {
    $dbo = $this->getDatasource();
    $logs = $dbo->getLog();
    $lastLog = end($logs['log']);
    return $lastLog['query'];
}

and then in any controller you call this like:

debug($this->{your model here}->getLastQuery());

answered Nov 27, 2013 at 23:18

skywalker

8361 gold badge10 silver badges18 bronze badges

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

1 Comment

jason Over a year ago

Thanks, at first this wasn't working. My log would always be null. Then I realized someone else on my team had put in validation in the models, which causes the query to never be run in the first place.

Erebus · Accepted Answer · 2013-11-28 00:09:29Z

0

Rather than trying to hack around in CakePHP, perhaps it would be easier to just log the queries with MySQL and do a tail -f on the log file? Here's how you can turn that on:

In MySQL, run SHOW VARIABLES; and look for general_log and general_log_file entries
If general_log is OFF, run SET GLOBAL general_log = 'ON'; to turn it on
In a terminal, run a tail -f logfile.log (log file location is in the general_log_file entry) to get a streaming view of the log as it's written to

This is very helpful for these circumstances to see what's going on behind the scenes, or if you have debug off for some reason.

answered Nov 28, 2013 at 0:09

Erebus

2,0382 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

Debugging SQL in controller

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related