Placing Database Rows Into Arrays Using PHP
After wasting too much time rewriting my PHP MySql queries for each project I worked on, I actually took my own advice, and created a re-usable function to handle basic queries. I have a PHP class that I use for this on my larger projects, but for the simple projects that come along, I was looking for something re-usable, easy to understand, and powerful. To that end, I'm attaching the sqlSelect function that I use in nearly all of my QUICK projects now. You know, the kind where the client wants to spend $50 or less and have a basic database app up and running. I don't take these that often but will usually help most of the designers I work with in a pinch.
Things you will notice from the PHP function:
- This function assumes that you have validated all user supplied SQL components and escaped your SQL appropriately. This is a must if you want to have any chance of stopping hackers.
- The sqlSelect function has an optional variable to pass in that can be used to set the array key that is returned. This is very useful when using well designed primary keys. You can call this function as simply as "sqlSelect( 'SELECT * FROM a', 'aId' );" where the primary key for table a is "aId", and you will be returned an array that has all of the records from table a with the array key being equal to the primary key of table a.
- I return associative arrays here. I know they are a little slower, but they are inherently easier to maintain. I try to make up for the slight efficiency loss of using associative arrays by making all SQL calls most efficient and only return required columns.
- The best part about this function in my mind, is that you can simply check the size of the array that is returned to determine if there was an error condition. If no rows are found, you get an array of "sizeof" 0. No checking repeatedly at the results and no fetching row after row in your code. (Makes your code easier to maintain and easier to read).
<?php
function sqlSelect( $sql, $keyFieldName = '' ) {
global $_SITE;
$retVal = array();
if ( $_SITE['Debug'] ) logMessage( "Sql: $sql" );
$rows = mysql_query( $sql );
if ( $rows ) {
while( $row = mysql_fetch_array( $rows, MYSQL_ASSOC ) ) {
if ( $keyFieldName == '' ) {
$retVal[] = $row;
} else {
$retVal[$row[$keyFieldName]] = $row;
}
}
} else {
logMessage( "\nSql Error: " . mysql_error() );
}
return $retVal;
}
?>
Side Notes about this PHP Function:
- I use a variable of $_SITE on many of my projects to store global config values. In this example, I have a global debug variable set that I use to turn on and off SQL logging (a simple way to track down bad or unexpected SQL results.
- I use a function to log messages that I have not included here. This can be removed, but I find it a simple way to handle error logging consistently across sites.
About the Author:
Esoomllub has been coding professionally for 20 years, taking up PHP coding in the last
6 or so years. He is now basically coding on his own sites, and only occasionally takes on
contract work. He is hoping his new blog will become a place to talk more about
website programming and webmaster revenues,
but it's not there yet!
