Simple PHP Database Access
Too often I see PHP code polluted with mysql_query statements. There are many reasons this is poor practise, and there is a better (and simpler) way.
The thing is, I should never see a mysql_query() statement (or any of the mysql_ functions) in the code flow. The most obvious problem with this approach is the application is tied down to using a MySQL database, as changing would require a large amount of code to be changed.
There are other issues, including:
- Code readability
- Error Message Inconsistencies
- Ensuring all queries are encoded and safe
- Error logging
- Testing
The solution is clearly to encapsulate the database access. One of the simplest ways to do this is to create a query() function. The following is a function I use in most of my PHP work:
/**
* Simplfied mysql queries
*
* Returns a multidimensional array like:
* 0 => field1, field2
* 1 => field1, field2
*
* Is able to accept multiple queries (separated by a
* semicolon). Only the result of the last query is
* returned. Returns false if no result returned.
*
* REGEX is used to split the sql by semicolons, but ignore the encoded characters (those in the query data)
* such as & and '
* The split is necessary because multiple queries are not officially supported by mysql_query
*
* @param string $sql the MySQL query to process
* @return mixed
*/
function query( $sql ) {
preg_match_all('/([^;]*?(&(\w)*?;)?[^;])+|[^;]+/', $sql, $matches );
$queries = $matches[0];
foreach( $queries as $query ) {
if( $query && $query != "'" ) {
$result = mysql_query( $query )
or raise_sql_error( mysql_error(), $query );
}
}
while( $row = @mysql_fetch_assoc( $result ) ) {
$output[] = $row;
}
return( isset($output) ) ? $output : false;
}
So there you have it. This can take multiple queries (separated by semicolons) and execute them. It also returns the result in a lovely associative array. Hopefully someone finds this code useful!
3 October 2007