Paginating Results with MS SQL

mainpgimageIn my last post, I showed you just how simple it is to retrieve paginated results using MySQL. Unfortunately, MS SQL doesn’t utilize the LIMIT command and doesn’t implement any easy way to retrieve paginated results. Instead, you actually have to run three nested queries with MS SQL in order to retrieve a specific subset of results. MS SQL supports a clause called “TOP” rather than “LIMIT” and the “TOP” clause only accepts a single parameter (the number of results to retrieve).

To retrieve paginated results with MS SQL, you will need to write the same query three times, reversing the ORDER BY clause in each query. You also have to use a little simple math in your queries.

Programming your own PHP framework, Part 1

building your own framework

The below article was authored by Terry Smith. Terry is an aspiring entrepreneur and PHP code ninja working for b5media. He spends his “weekends” and “free time” working on all sorts of crazy ideas and one day wants to rule the world (or a small part of it)! This is first part of the tutorial post. Second part can be found here.

I’ve always been torn about posting code on this site, since I’ve historically (if I can use that word) wanted to keep it about business ideas.  However, since I’ve started working for b5media, I’ve had a lot less time to work on my own projects; therefore I think I’ll start talking about some code.

These posts are geared towards people who know programming (specifically to people who code in PHP, but if you can read the code, you can port it to most other languages).

As with most engineers, I like to re-create tools I use on a regular basis.  Some I’ve tried and failed at for various reasons (3D game engine), but one of the ones I’ve always wanted to try is a PHP framework of my own.  So last weekend, I sat down and wrote a basic framework that I would enjoy using.  This series of articles will talik about how I did that.  So, without further ado:

There are several components of a web framework: URL rewriting, database abstraction, an MVC framework, a templating system and plug-ins.  All of these are optional and you can pick and choose which you want to include in your framework and how you want to include them.

In this first article, I will cover URL rewriting.

Optimize Your JPEGs with PHP

If you’re anything like me, you put a lot of images and photographs on your Web site without opening them in PhotoShop to optimize them for the Web, first. Generally, if I download an image from the Web or I get an image e-mailed to me to add to the Web site, as long as it’s the right size, I just upload it and move on.

Unfortunately, however, those images are usually quite bloated and not compressed properly for the Web. With PHP, though, you can easily optimize an entire directory full of JPEG and PNG images with one simple script without losing any noticeable quality.

Need PHP Developer – Max Pay $7/hr.

Are you looking for a job as a PHP developer? oDesk has a position posted for a talented PHP developer for $7/hour. Check out all of the details here and a listing of the applicants. The company is based in the U.S. and is seeking someone for more than 30 hours a week on a fulltime basis.

Here’s the job description:

I need a reliable and talented php-developer who can use best practice development (version controlling, well commented and tested code, etc.) to develop a peer-2-peer lending site with a team.

Is this the going rate for PHP developers these days? Doubt it.

There’s a really interesting thread on Reddit about this job posting. It seems like many of the comments note that $7 in India is worth more than $40 in the U.S.

Zend Releases Studio 7.0 Beta

Today, Zend released the latest beta version of their Zend Studio application. Zend Studio is an integrated development environment (IDE) for PHP developers. Although the software usually requires a commercial support license (which currently runs $399 for one year or $717 for three years), the beta version of Zend Studio 7.0 is available as a free trial download for the time being.

You can download the beta software from Zend’s Web site.

Listing the Contents of a Directory With PHP

Following is a quick tutorial explaining how to list the contents of a directory with PHP. Much of this information is taken from the official PHP documentation of the opendir() function.

To begin with, you need to determine which directory you want to list. Start by storing the location of that directory in a variable. If you want to list the contents of the directory in which your script is running, you can use the realpath function to find the full path to that directory. That would look something like:
$dir = realpath('./');
The function above will find the full, absolute path to the current directory and store it as the “$dir” variable.

Next, write yourself a function that looks similar to:
define('HOMEPATH','/home/user/directory'); // Change this to match the absolute path to the root of your Web site (no trailing slash)
define('HOMEURL',''); // Change this to match the URL to the root of your Web site (no trailing slash)

function listdir($dir) {
$list = array();
// Open a known directory, and proceed to read its contents
if (is_dir($dir)) {
if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
if($file !== '.' && $file !== '..' && $file !== 'index.php') {
if(is_dir($dir.'/'.$file)) {
$tmparr = listdir($dir.'/'.$file);
if(count($tmparr)) {
$list[] = '<li><strong>'.$file.'</strong><ul>'.implode('',$tmparr).'</ul></li>'."\n";
else {
$list[] = '<li><a href="'.str_replace(array(HOMEPATH,'&',' '),array(HOMEURL,'&amp;','%20'),$dir.'/'.$file).'">'.str_replace(array('&'),array('&amp;'),$file).'</a></li>'."\n";
return $list;

The function above will recursively search the directory and create an unordered list of links to each of the files found inside. It will ignore “index.php” in each of those directories.

To run the function and print the list onto your screen, you would use code similar to:
$list = listdir($dir);
if(count($list)) { // Check to make sure the list returned had at least one item
echo '<ul>'.implode('',$list).'</ul>';

That’s about all there is to it. I hope that helps someone.

Edit: On July 14, 2009, I updated the code in this post due to a potential bug. The code used in this post has been tested and is available in a ZIP file in this post.