How to use echo in a query with prepared statement?

5

I would like to know how to use echo in a query with prepared statement, to be able to see how it was mounted by php before it was executed, thus facilitating visualization of the error when bind_param is done ...

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 the query extensively and not just the results of its execution, eg:

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

I looked for a solution and the only one I found was using Zend so it's not possible for me ... Any suggestions?

    
asked by anonymous 11.03.2014 / 19:43

2 answers

5

If the goal is to see the values corresponding to 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 second argument within another ( $consulta ). array_fill () creates an array with the% regex of repository with% being the number of elements will be defined by '/\?/' and lastly the substitution occurs with preg_replace , it works so it looks for the first regex and replaces it with the first value found in $count and so on until the end of the array arrives. The last argument $valor means the maximum number of overrides made.

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++;
}

output:

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
    
13.03.2014 / 15:10
7

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

Notice the mysqli_stmt documentation attributes, at most you'll be able to extract the $ errno (int) or the $ error (string).

If the query gets triggered (even if with errors), for debugging purposes I find it useful to activate the MySQL queries log. Then you can follow up with a tail -f in the log file. See how to enable:

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