Jump to content

Improved method to display PDO query


NotionCommotion

Recommended Posts

I've used a showQuery() method for years which accepted a SQL string and data array.  Sometimes, I just have the PDO statement and I need to add temporary script just to keep track of the SQL string.  Was looking at debugDumpParams() today which prompted me to create the new showStmtQuery() method.  My approach of extracting the table from debugDumpParams's printed results is a little clunky.  Any recommendations?  Could/should other information from debugDumpParams's output be used?  I typically don't use bound parameters but know I should more often.  Suggestions how this could be changed to work with bound parameters?  Thanks

 

function showQuery(string $sql, arrray $data, bool $keepLineBreaks=false):string {
    $keys = [];
    $values = [];
    foreach ($data as $key=>$value) {
        $keys[] = is_string($key)?'/:'.$key.'/':'/[?]/';
        $values[] = is_numeric($value)?$value:"'$value'";
    }
    $sql = preg_replace($keys, $values, $sql, 1, $count);
    return $keepLineBreaks?$sql:str_replace(array("\r", "\n"), ' ', $sql);
}
function showStmtQuery(\PDOStatement $stmt, array $data, bool $keepLineBreaks=false):string {
    ob_start();
    $stmt->debugDumpParams();
    $sql = ob_get_contents();
    ob_end_clean();
    //$sql = strtok(substr($sql, strpos($sql, '] ')+2), "\n");
    $start=strpos($sql, '] ')+2;
    $sql = substr($sql, $start, strpos($sql, "\nParams:") - $start);
    return showQuery($sql);
}

$data=[5,50];
$sth = $pdo->prepare('SELECT *
    FROM my_table
WHERE id > ? AND id < ?');
echo(showStmtQuery($sth, $data).PHP_EOL);   //SELECT * FROM my_table WHERE id > 5 AND id < 50

$data=['lowId' => 5, 'highId' => 50];
$sth = $pdo->prepare('SELECT *
    FROM my_table
WHERE id > :lowId AND id < :highId');
echo(showStmtQuery($sth, $data).PHP_EOL);   //SELECT * FROM my_table WHERE id > 5 AND id < 50


 

Edited by NotionCommotion
Changed to use original showQuery function
Link to comment
Share on other sites

Instead of ...

    ob_start();
    $stmt->debugDumpParams();
    $sql = ob_get_contents();
    ob_end_clean();
    //$sql = strtok(substr($sql, strpos($sql, '] ')+2), "\n");
    $start=strpos($sql, '] ')+2;
    $sql = substr($sql, $start, strpos($sql, "\nParams:") - $start);

why don't you use ...

$sql = $stmt->queryString;

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.