Debugging PDO-MySQL queries

[ReggieTheDog]

Guys, is there any particular method/technique for debugging queries passing through PHP PDO. My issue is that the query I created in mySQL workbench - worked a treat. I placed this same query on PHP page, passed to mySQL using PDO prepared statements and no results get returned. I have display errors on and error reporting all-STRICT. Looked in the error log and nothing, not a bean. Tried to look if there was an issue with the query itself so used a proxy to capture what as sent. Cut and paste it straight into mySQL workbench and it worked… returned my one row of test data in the flashest of flashes… So I’m a bit puzzled as to what is going wrong here. Any tips?

[ReggieTheDog]

Sorry for wasting everyones time - found my rogue param. Still surprised that nothing came up though in the error log. Moral of the story is - do work on php/mysql when your dosed up with man flu…

[SamA74]

@ReggieTheDog

Still surprised that nothing came up though in the error log.

You probably need to set up your PDO error mode.
There is a current discussion about PDO and error handling in the PHP forum:-

When I develop with PHP and MySQL[PDO] can I use two try blocks like below?

when I develop with PHP and MySQL[PDO] can I use two try blocks[as of security] like below? private function add_banned_user() { $username = $this->username; $tmstmp = time(); try { $conn = $this->conn; $sql = "INSERT INTO banned_users (username, timestamp) VALUES ('$username', $tmstmp)"; if ($conn->query($sql) === TRUE) { //echo " You(".$username.") have been banned!"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } } catch(PDOException $e…

I’ll move this there too, as you are more likely to get help with setting up your pdo error handling there.

[ReggieTheDog]

@mymac1 @SamA74 - PDO error mode set up correctly but thanks for putting me on to the PHP thread…Looks like a heated debate. I think I’ll just read for now, rather than adding fuel to the fire

[colshrapnel]

1. Make sure that PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION
2. Make sure that php.ini directive log_errors is set to on
3. Make sure that error reporting is set to E_ALL

This is all you can do on the PHP/PDO side.

The rest is irrelevant to PDO but rather a common debugging technique

1. Verify all the input variables
2. Verify every operation’s result.
3. Then double-check every operation’s result.

Looks like you had an issue with the last one.
It is not clear that was exact issue with the “rogue param” but I assume it’s just a variable having wrong value. In this case your query from the proxy should contain this wrong value. So you had to re-check that query from the proxy.