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

Question

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?

Answer 1

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