Menu
I am working on a PHP application that takes queries in a text box and returns paginated results. As part of the application I want to report the running time of the query.
Here is what I have done so far.
I started off by enabling profiling in by directly entering in the text box and running the script:
set global profiling = 1
Using the provided text box I enter the following query:
select @@profiling
And get:
1
Finally, I run the query as so:
select * from log
However, when I run the command for to profile the query:
show profiles
I receive no result and no content displayed on the page.
Since I see no table after the command "show profiles" does this mean that there are not sufficient privileges or am I missing another step?
I followed the procedure on:
Measuring actual MySQL query time
Please advise.
My PHP code is as follows:
<?php
if($_POST)
{
$db = new PDO('mysql:host=localhost;dbname=<dbname>;charset=utf8', 'user', 'pass');
$stmt = $db->prepare($_POST['query']);
$stmt->execute();
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$errmsg = $stmt->errorInfo()[2]; //Output the error message - Index 2 of the array
echo $errmsg;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Log</title>
</head>
<body>
<form method="post" id="queryform">
<div class="label">
<span class="label">Enter SQL Query</span>
</div>
<div class="input">
<input type="text" name="query" size="150" value="<?=$_POST['query']?>" />
</div>
</form>
<? if (isset($records)): ?>
<table border="1">
<? foreach($records as $record): ?>
<tr>
<? foreach($record as $colname => $value): ?>
<td>
<?=$value;?>
</td>
<? endforeach; ?>
</tr>
<? endforeach; ?>
</table>
<? endif; ?>
</body>
</html>
Any help would be appreciated.
884
This worked like a charm!
$db->query('set profiling=1'); //optional if profiling is already enabled
$db->query($_POST['query']);
$stmt = $db->query('show profiles');
$db->query('set profiling=0'); //optional as well
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$errmsg = $stmt->errorInfo()[2]; //Output the error message
UPDATE (The following now works on innodb on my present setup)
$db->query('set profiling=1'); //optional if profiling is already enabled
$db->query($_POST['query']);
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration']; // get the first record [0] and the Duration column ['Duration'] from the first record
Result of (show profiles) from phpmyadmin.
Query_ID Duration Query
1 0.00010575 SELECT DATABASE()
Getting the actual (absolute) execution time of the last query in PHP (excluding network latency etc)
76
How about this:
$start = microtime(true);
$stmt->execute();
$end = microtime(true);
echo "This query took " . ($end - $start) . " seconds.";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
