Let Your Database Do The Work For You

In the below examples I’ll use data from a fictional database table named “users”. We’re looking to return the 10 most recently added users with a date of birth later than January 1st 1970.

Bad practice

One way I’ve seen it done before is like this:

$query = mysql_query( "SELECT * FROM `users`" );
while( $row = mysql_fetch_assoc( $query ) ){
    $dob = strtotime( $row['dob'] );
    $joined = strtotime( $row['joined'] );
    if( $dob >= 0 ){
        $array[$joined] = $row;
    }
}
krsort( $array, SORT_NUMERIC );
array_slice( $array, 10 );
foreach( $array as $item ){
    echo 'Name: ' . $item['name'] . '';
    echo 'Join Date: ' . $item['joined'] . '';
    echo 'Date of Birth: ' . $item['dob'] . '';
    echo '';
}

Good practice

The alternative uses some SQL techniques to do all of that data sorting within the database query.

$dob_limit = date( 'c', 0 );
$query = mysql_query( "SELECT `name`,`joined`,`dob` FROM `users` WHERE `dob` >= $dob_limit ORDER BY `joined` DESC LIMIT 10" );
while( $item = mysql_fetch_assoc( $query ) ){
    echo 'Name: ' . $item['name'] . '';
    echo 'Join Date: ' . $item['joined'] . '';
    echo 'Date of Birth: ' . $item['dob'] . '';
    echo '';
}

As you can see, the latter script should look far simpler, is easier to write, and works much quicker and efficiently.

Note also that in the second script the fields after the SELECT statement have been specified rather than leaving a wild-card asterisk (*). The asterisk returns all data in the row that the SQL statement found a match in, and the more data we have in a row the more data could become redundant as we extract it.

For example, consider that the users table had extra fields containing first name, surname, website URL, small personal biography, home town, favourite film, favourite food, and so on. For each row you extract, all seven of those become unused pieces of data in the memory on your server and possibly even the computers of your users. Multiply that seven by each row in your database, then by how many times the query will be called, and things start to add up pretty quickly.

The point is then that if you start with a clear idea on what you actually need in order to use your script, you can be specific in what data you need to collect from your database. Doing so decreases your page load times and lowers your bandwidth, usually with dramatic results.