Re: Getting time of a postgresql-request

On Tue, 18 Aug 2009 06:25:57 +0200, Russell Smith <mr-russ(at)pws(dot)com(dot)au>
wrote:

> Kai Behncke wrote:
>>
>> But I would like to get it in a php-script, like
>>
>> $timerequest_result=pg_result($timerequest,0);
>>
>> (well, that does not work).
>>
>> I wonder: Is there another way to get the time a request needs?
>> How do you handle this?
>>
> $time = microtime()
> $result = pg_result($query);
> echo "Time to run query and return result to PHP: ".(microtime() -
> $time);
>
> Something like that.
>
> Regards
>
> Russell
>

I use the following functions wich protect against SQL injections, make
using the db a lot easier, and log query times to display at the bottom of
the page.
It is much less cumbersome than PEAR::DB or pdo which force you to use
prepared statements (slower if you throw them away after using them just
once)

db_query( "SELECT * FROM stuff WHERE a=%s AND b=%s", array( $a, $b ))

db_query( "SELECT * FROM stuff WHERE id IN (%s) AND b=%s", array(
$list_of_ints, $b ))

------------

function db_quote_query( $sql, $params=false )
{
// if no params, send query raw
if( $params === false ) return $sql;
if( !is_array( $params )) $params = array( $params );

// quote params
foreach( $params as $key => $val )
{
if( is_array( $val ))
$params[$key] = implode( ', ', array_map( intval, $val ));
else
$params[$key] = is_null($val)?'NULL':("'".pg_escape_string($val)."'");;
}
return vsprintf( $sql, $params );
}

function db_query( $sql, $params=false )
{
// it's already a query
if( is_resource( $sql ))
return $sql;

$sql = db_quote_query( $sql, $params );

$t = getmicrotime( true );
if( DEBUG > 1 ) xdump( $sql );
$r = pg_query( $sql );
if( !$r )
{
if( DEBUG > 1 )
{
echo "<div class=bigerror><b>Erreur PostgreSQL :</b><br
/>".htmlspecialchars(pg_last_error())."<br /><br /><b>Requête</b> :<br
/>".$sql."<br /><br /><b>Traceback </b>:<pre>";
foreach( debug_backtrace() as $t ) xdump( $t );
echo "</pre></div>";
}
die();
}
if( DEBUG > 1) xdump( $r );
global $_global_queries_log, $_mark_query_time;
$_mark_query_time = getmicrotime( true );
$_global_queries_log[] = array( $_mark_query_time-$t, $sql );
return $r;
}