How do I use echo in a query with Prepared statement?

Question

I would like to know how to use echo in a query with Prepared statement, to be able to visualize how it was mounted by php before it was executed, thus facilitating the visualization of the error at the time of bind_param...

Example:

$Apagar = $conn->prepare("DELETE FROM mensagens WHERE id IN (?)");
$Apagar->bind_param('s',$Mensagens);
print_r($Apagar);exit;

I would like this to return me the full query and not just the results of its execution, example:

DELETE FROM mensagens WHERE id in (1,2,3,...);

I looked for a solution and the only one I could find was using Zend so it’s unviable for me... Any suggestions?

Answer 1

What you want to do is not possible via php-mysqli.

Note the attributes of mysqli_stmt in the documentation, at most you will be able to extract the $Errno (int) or the $error (string).

If the query is triggered (even with errors), for debugging purposes I think it’s nice to enable the Mysql queries log. Then you can follow in real time with a Tail -f in the log file. See only how to enable:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/tmp/mysql.log";
SET GLOBAL general_log = 'ON';

Answer 2

If the goal is to see the corresponding values the queries that substitution function.

function setValor($consulta, $valores){
    $count = substr_count($consulta, '?');
    $interrogacoes = array_fill('0', $count, '/\?/');
    $consulta_original = preg_replace($interrogacoes, $valores, $consulta, 1);

    return $consulta_original;
}

substr_count() returns the number of occurrences found (in the case ?) of a string passed as the second argument within another($consulta). array_fill() creates an array with the replace regex '/\?/' the number of items shall be defined by $count and finally the substitution occurs with preg_replace, works as follows it searches for the first regex and replaces it with the first value found in $valor and so on until the end of the array. The last argument 1 means the maximum number of replacements performed.

example:

$consultas = array(
            'INSERT INTO tabela(nome, email, idade, endereco) VALUES (?,?,?,?)',
            'SELECT * FROM tabela WHERE id IN(?,?)',
            'DELETE FROM tabela where id = ?'
) ;

$valores = array(
                array('joao', '[email protected].', '20', 'rua xx'),
                array(99, 70),
                array(48)
);


$i =0;
foreach($consultas as $item){
    echo  setValor($item, $valores[$i]) .'<br>';
    $i++;
}

exit:

INSERT INTO tabela(nome, email, idade, endereco) VALUES (joao,[email protected].,20,rua xx)
SELECT * FROM tabela WHERE id IN(99,70)
DELETE FROM tabela where id = 48