Export "query" from "mysqli->prepare"

Question

Is it possible to export the query formatted by mysqli::prepare and ::bind_param?

Example:

<?php
$mysqli = new mysqli('host', 'user', 'pass', 'table');
if(mysqli_connect_errno()){
    printf('Connect failed: %s\n', mysqli_connect_error());
    exit;
}

$data=7290;

if ($stmt = $mysqli->prepare('SELECT `id`,`info` FROM `propertys` WHERE id>?')){
    $stmt->bind_param('i',$data);
    $stmt->execute();
    $stmt->bind_result($id,$info);
    while($q=$stmt->fetch()){
        echo $id,': ',$info,'<br>';
    }
    $stmt->close();
}
$mysqli->close();
?>

I would like to export the QUERY functions performed by mysql::prepare and bind_param so (this is an imaginary example):

if ($stmt = $mysqli->prepare('SELECT `id`,`info` FROM `propertys` WHERE id>?')){
    $stmt->bind_param('i',$data);
    $stmt->execute();
    echo $stmt->exportQuery();//Function does not exist, just for example

The function ::exportQuery would print like this:

SELECT `id`,`info` FROM `propertys` WHERE id>7290

is there any solution?

Thanks.

Answer 1

I know that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side. PHP should never have access to the query string combined with its parameters.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PHP, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

---

Re your comment:

@Baily is correct, MySQL has no client-side solution to return the full query with parameters interpolated. It's not the fault of PHP.

To enable the logging that I mention above, use this command, either in the MySQL client or submitted from PHP via an API:

SET GLOBAL general_log = ON;

You should turn off the log when you're done collecting information, because it does cost some overhead to be logging every query.

SET GLOBAL general_log = OFF;

PS: Changing the logging settings dynamically requires MySQL 5.1 or later. In earlier versions, you have to restart mysqld when you change logging.