Voting

The Note You're Voting On

11 years ago
Debugging parameterised queries can be tedious, if you want to paste the query directly into PSQL. Here is a trick that helps:

<?php
$sql = "SELECT * from table WHERE col_a = $1 and col_b=$2 and col_c=$3";
$params = array (42, "a string", NULL);

$debug = preg_replace_callback( 
        '/\$(\d+)\b/',
        function($match) use ($params) { 
            $key=($match[1]-1); return ( is_null($params[$key])?'NULL':pg_escape_literal($params[$key]) ); 
        },
        $sql);

echo "$debug";
//prints:   SELECT * from table WHERE col_a = '42' and col_b='a string' and col_c=NULL
?>

This works correctly, except in the (unusual) case where we have a literal $N;  the regexp replaces it where it shouldn't.  For example:
<?php
//Both  ' ... $1 ... '   and  $1   get replaced; the former is wrong, the latter is right.
$sql = "SELECT 'Your bill is for $1' AS invoice WHERE 7 = $1";
$params = array(7);
//$debug:  SELECT 'Your bill is for $7' AS invoice WHERE 7 = '7'"
?>
<< Back to user notes page