How can I view the contents of a prepared statement?

Question

Hi, I'm working on learning to use prepared statements with mysqli in PHP and usually, if I'm having a problem with a query I just echo it to the screen to see what it looks like as a first step.

How can I do that with a prepared statement?

I'd like to see the SQL statement after the variables are substituted.

Answer 1

Using prepared statements :

• When you prepare the statement, it is sent to the MySQL server
• When you bind the variables + execute the statement, only the variables are sent to the MySQL server
• And the statement + bound variables are executed on the MySQL server -- without it re-doing the "preparation" each time the statement is executed (which is why prepared statements can be good for performances when the same statement is executed several times)

There is no "building" of an SQL query on the PHP side ; so, there is no way to actually get that query.

Which means that if you want to see an SQL query, you have to use... Well, SQL queries, and not prepared statements.

Answer 2

Agreeing with Pascal MARTIN (+1) so I suggest another technique for debugging: var_dump() or log every variable you're inserting into the statement, that way you should be able to figure out if it is wrong data or logically wrong SQL.

Answer 3

• You can use PDOStatement->debugDumpParams to get some informations about the prepared statement (in case you're using pdo).
• Prepared statements are logged in MySQL's general log:

For prepared statements that are executed with the mysql_stmt_prepare() and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.
[...] the server writes the following lines to the general query log:
Prepare [1] SELECT ?
Execute [1] SELECT 3

So for debugging purposes active the general log and keep an eye on that file.

edit: oh, the question has a [mysqli] tag... completely overlooked that.
If the statement isn't executed at all have you (double/tripple) checked that no error occurred along the way?

echo "<pre>Debug: start</pre>\n";

$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
if ($mysqli->connect_error) {
  die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('CREATE TEMPORARY TABLE foo (id int auto_increment, x int, primary key(id))');
if ( false=== $result) { 
 die('error : '. $mysqli->error);
}

$stmt = $mysqli->prepare('INSERT INTO foo (x) VALUES (?)');
if ( false===$stmt ) {
  die ('prepare() failed: ' . $mysqli->error);
}

$result = $stmt->bind_param('i', $x);
if ( false===$result ) {
  die('bind_param() failed');
}

$x = 1;
$result = $stmt->execute();
if ( false===$result ) {
  die('execute() failed: '.$stmt->error);
}

echo "<pre>Debug: end</pre>\n";