SQL Output Debugging with Zend Framework

Posted: Friday 20th February 2009

Zend Framework – at first I wasn’t much impressed. Bloated, over-rated, excessive. I was wrong, now I’m loving it 100%!

As I build more and more larger and more complex websites and web-based applications based around the Zend Framework, I attempt to improve the systems I build every time – more efficient coding, quicker database queries, less queries, etc.

One thing that I’ve wanted for a while, though, is the ability to view the time taken to run each query on any particular page, the total time to execute all queries, and the ability to view all queries. By this I mean I don’t want to re-code all my class’s and controllers in order to show the SQL, but instead create a simple bit of code to do it for me. Surely, Zend being its awesome-self should support something like this?

After a little searching around – why yes, yes it does! :)

Here’s my version of a simple output debug-type thing to show total query execution time, each query itself and each queries time, the average execution time & some more. Anyway, on your bootstrap file where you’re defining what database to use, enter this below…

'profiler' => true

For example then…

'host'     => 'localhost',
'username' => 'dbuser',
'password' => 'dbpass',
'dbname'   => 'dbname',
'profiler' => true

Then, further down on your bootstrap file, after $controller->dispatch(); copy/paste this…

// Zend framework debug cool stuff - www.dazecoop.co.uk
$debug = false;
if (($debug == true) || (strstr($_SERVER['REQUEST_URI'],'debug=1'))) {
echo '<div style="font-family:arial;padding:10px;background:#efefef;font-size:11px;position:absolute;top:0px;right:0px;">
<a href="javascript:void(0);" onclick="if ($(\'#debug-panel\').css(\'display\')==\'block\'){$(\'#debug-panel\').slideUp();}else{$(\'#debug-panel\').slideDown();}" style="color:#000;">Debug</a></div>';
echo '<div id="debug-panel" style="width:850px;font-family:arial;position:absolute;top:0px;left:0px;display:none;background:#efefef;font-size:11px;color:#000;padding:20px;">';
$profiler = $db->getProfiler();
$totalTime    = $profiler->getTotalElapsedSecs();
$queryCount   = $profiler->getTotalNumQueries();
$longestTime  = 0;
$longestQuery = null;
foreach ($profiler->getQueryProfiles() as $query) {
if ($query->getElapsedSecs() > $longestTime) {
$longestTime  = $query->getElapsedSecs();
$longestQuery = $query->getQuery();
}
$queries .= '<small>('.round($query->getElapsedSecs(),5).' seconds)</small> '.$query->getQuery().'<hr style="border-top:1px solid #cccccc" />';
}
echo 'Executed <strong>' . $queryCount . '</strong> queries in <strong>' . $totalTime . ' seconds</strong>' . "<br />";
echo 'Average query length: ' . $totalTime / $queryCount . ' seconds' . "<br />";
echo 'Queries per second: ' . $queryCount / $totalTime . "<br />";
echo 'Longest query length: ' . $longestTime . "<br />";
echo "Longest query: \n" . $longestQuery . "<br />";
echo '<hr />'.$queries;
echo '</div>';
}

Although making sure you’ve got jQuery already installed before using the above code. And make sure to subscribe to my RSS feed for possible future awesomeness :)

Tagged , , , , | 6 Comments

Twitter latest WordPress plugin, with “how long ago”

Posted: Thursday 2nd October 2008

After saying on my previous post about using Twitter and (maybe) a new design for Dazecoop, I needed to find a WordPress Plugin to grab the RSS feed of my Twitter account, then display it nicely on my WP header.

Simple Twitter seemed to fit the bill nicely, and within about 40 seconds of installing it, my latest “tweet” was in my header for all to see – not only that but Simple Twitter includes a caching feature as not to bombard Twitter too much.

After installing it though, I was a little disappointed to see that it didn’t return how long ago my latest tweet was made. Booger! I had a quick look at the code and decided to hack-my-way in so grab this information…

Basically, within the function update_twitter_message(), I added in this code below line 141:

// ** modifications to show how long ago tweet was made

// ** quick & dirty hack - c'mon, its 1am!...

// ** accuracy all depends on cache time, i use re-cache every 15 mins

// ** by David Cooper - www.dazecoop.co.uk

$timenow = time();

$pubdate = strtotime(get_message_from_url($url, 'pubDate', 'item'));

$minutespast = round(($timenow - $pubdate) / 60);

$hourspast = round($minutespast / 60);

$dayspast = round($hourspast / 24);

if ($minutespast < 2) {

	$returnTimepast = 'a few minutes ago';

} elseif ($minutespast < 55) {

   $returnTimepast = 'roughly ' . $minutespast . ' minutes ago';

} elseif (($minutespast > 55) && ($minutespast < 110)) {

   $returnTimepast = 'about an hour ago';

} elseif ($hourspast < 23) {

   $returnTimepast = $hourspast . ' hours ago';

} elseif (($hourspast > 23) && ($hourspast < 47)) {

   $returnTimepast = 'yesterday';

} else {

   $returnTimepast = $dayspast . ' days ago';

}

$title = get_message_from_url($url) . ' <small>' . $returnTimepast . '</small>';

//$title = get_message_from_url($url);

// ** end modifications

The $title variable is the only thing that I’ve changed, original is:

$title = get_message_from_url($url);

And my version is now:

$title = get_message_from_url($url) . ' <small>' . $returnTimepast . '</small>';

Job done! And works wonders

Tagged , , | 1 Comment