如何查看调用查询的最终文本到mysqli-> prepare？

After code like this:

$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
$stmt->bind_param("s", $city);
$stmt->execute();
$stmt->bind_result($district);
$stmt->fetch();
printf("%s is in district %s
", $city, $district);

How Do I See The Actual SQL Statement That Was Executed?

(It Should Look Something Like "SELECT District FROM City WHERE Name='Simi Valley';")

I already realize that in this simplistic case it would be very easy to simply reconstruct the query... but how can I access it in a general way that will work for very complicated prepared statements, and cases where I don't necessarily already understand the intended structure of the query, etc. Isn't there some function or method that can be called on the statement object that will return the actual text of the SQL query, after binding?

When you are using prepared statements, there is no "SQL query" :

  • First, you have a statement, that contains placeholders
    • This statement is sent to the DB server, and prepared there
    • which means that the SQL statement is analysed, parsed, and that some data-structure representing it is prepared in memory
  • And, then, you have bound variables
    • which are sent to the server
    • and the prepared statement is executed -- working on those data

But there is actualy no re-construction of an actual real SQL query -- neither on the PHP side, nor on the database side.

So, there is no way to get the prepared statement's SQL -- as there is no such SQL.


If you need to see some informations, for debugging purposes, as your said, you'll generally have two kind of options :

  • Either create the SQL query that would correspond to the prepared statement + binding by hand.
  • Or ouput the code of the statement, with the placeholders ; and the list of data
    • This means you will not have a real SQL query that can be executed
    • But it'll generally be enough, to help with debugging

PDO simulates binding if you set it like that; in this topic you can read about the debugDumpParams statement, which is also in the PHP documentation. There is no way, however, to check if the value substitution happened properly when you leave it to the real sql engine; one workaround could be a SELECT with the field placeholders in it so that in the result you get the actual string values:

SELECT :field1 as field1, :field2 as field2

If you bind and fetch this one, it will contain the values:

Array
(
    [field1] => Lorem ipsum
    [field2] => dolor sit amet
)

With this, you can build your own debug function which stitches the original query string together using sql's CONCAT - but you won't learn a lot from this since SQL is quite reliable when it comes to parameter binding :)