PDO - Full Debug Query?

Question

PHP

Question for people who constantly use PDO.
I'm planning to switch to PDO, but I'm very confused by the inability to view the final query sent to the database with the parameters (: id : name) already substituted in a simple way. It is very convenient when debugging, for example, in PMA. Some invent such bicycles stackoverflow.com/a/1376838/1101589 . Do you somehow solve this or is it not relevant for you, maybe you are debugging in a different way? I know that server prepared statements work differently, but I don't really plan to use them.

Answer 1

Based on the logic of how PDO works with the database, for normal debugging you still have to write your own bike, because. First, I want to see the full query string, then find out where the query was called, how long it was running, etc. etc.
The most correct way is to inherit from PDO and PDOStatement, and already make “blackjack with whores” for your wrapper, for example, like here daveyshafik.com/archives/605-debugging-pdo-prepared-statements.html
The link above is far from the best solution, I I would override the execute method in PDOStatement and other similar methods so that, for example, if the debugging option is enabled, they themselves would put the information I need in the right place, and when debugging is disabled, they simply skip requests to PDO.

Answer 2

On the dev machine, I usually turn on the logging of all queries using the SQL server, and see what happens there.
Here is an example (MySQL):

$st = $pdo->prepare('SELECT * FROM user WHERE id=:id');
$id = 2;
$st->bindParam(':id', $id, PDO::PARAM_INT);
$st->execute();
$r = $st->fetchAll();

Outputs a line to the MySQL log

 6 Query	SELECT * FROM user WHERE id=2

When using PostgreSQL, this looks a little different:

LOG:  duration: 2.669 ms  parse pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
LOG:  duration: 0.126 ms  bind pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
DETAIL:  parameters: $1 = '20', $2 = '0'
LOG:  duration: 0.118 ms  execute pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
DETAIL:  parameters: $1 = '20', $2 = '0'
LOG:  duration: 0.172 ms  statement: DEALLOCATE pdo_stmt_00000af1

If desired, you can send the SQL log to the same file as the debug PHP log, so as not to switch between terminal windows with "tail -f" that monitor the logs.

Answer 3

If you don't plan to use server-side placeholders, then I don't see any point in using the built-in PDO parser at all.
We write our own and have
a) a bunch of additional placeholders
b) the ability to substitute a placeholder in a piece of the request, and not just in the whole
c) debug output.

Answer 4

www.php.net/manual/en/pdostatement.debugdumpparams.php
Isn't it?

Answer 5

Same question as yours, solution is a bit hacky but works:
stackoverflow.com/questions/7716785/get-last-executed-query-in-php-pdo

Answer 6

I don't have a specific answer to the question posed, but just to make it clearer why it works the way it does. PDO uses parameterized queries, which means that the query is stored in the database exactly as

SELECT *
FROM `table`
WHERE `id` = :id

the next time this query is called, but with different parameters, the database engine will no longer compile this query, it will simply execute it with the new parameters. We can say that this is similar to stored procedures.

Answer 7

it is possible to fence mountains in the code for debugging SQL, but what for it? after all, our task is essentially to identify who exactly comes to the final Mysql server. There is Neor Profile SQL tool for this .
And there is no difference what to monitor: PDO or regular constructs.