On January 21, 2009 12:37:07 Jay Moore wrote:
> This is a MySQL class I use and I wanted to get everyone's thoughts on
> how/if I can improve it. This is for MySQL only. I don't need to make
> it compatible with other databases. I'm curious what you all think.
I definetly think that using a DB class is a really good idea, but the
interface to your object could use some improvement.
>
> Sample usage:
> $value = 'value';
> $sql = new do_mysql();
> $sql->sanitize($value);
> $sql->query = "SELECT * FROM `wherever` WHERE `field` = '$value'";
> $sql->process('dbname');
> $sql->close();
>
> if ($sql->num_rows())
> {
> while ($row = $sql->fetch_array())
> {
> do stuff;
> }
> }
One of the goals of encapsulating DB access should be to hide some of the
tediousness of database access, however, using the interface for your object
you're still writing just as many (if not more) lines of code as you would by
simply using the mysql_* functions directly.
As an example of how you can put this into practice, here's the interface to
the DB object that I use:
The object is implemented as a singleton. When retrieved initially the
constructor deals with setting up the connection using constant defined in the
class for username, password, dbName and server address. The instance itself
wraps a PDO connection and provides the following interface:
// Query functions
query($query, $queryName = null, $taskName = null);
prepare($query, $queryName, $taskName = null);
execute($queryName, array $params = array(), $taskName = null);
static sanitize($string);
// Data retrieval functions
getResults($queryName, $taskName = null);
getColumn($queryName, $taskName = null, $columnIndex = 0);
getRow($queryName, $taskName = null, $rowIndex = 0);
getCell($queryName, $taskName = null, $rowIndex = 0, $columnIndex = 0);
// Storage control
reset($queryName, $taskName = null);
resetAll($taskName = null);
The interface is clean and lets me handle most common cases with readable
code:
// Retrieve one piece of data from the database
try {
$db = DB::getInstance();
$db->prepare('SELECT COUNT(*) FROM users WHERE id=:userid', 'getNumUsers');
$db->execute('getNumUsers', array(':userid' => $userId));
$numUsers = $db->getCell('getNumUsers');
// Retrieve a row from the database
if($numUsers == 1) {
$db->prepare('SELECT * FROM users WHERE id=:userid', 'getUserInfo');
$db->execute('getUserInfo', array(':userid' => $userId));
$userInfo = $db->getRow('getUserInfo');
// Retrieve multiple rows from the data
$db->prepare('SELECT friend_id FROM user_friends WHERE user_id=:userid',
'getUsersFriends');
$db->execute('getUsersFriends', array(':userid' => $userId));
foreach($db->getColumn('getUsersFriends') AS $friendId) {
$db->execute('getUserInfo', array(':userid'=> $friendId));
$friendInfo = $db->getRow('getUserInfo');
// .....
}
}
$db->reset('getNumUsers');
$db->reset('getUserInfo');
$db->reset('getUsersFiends');
} catch(DBException $exception) {
echo $exception;
}
Since the object wraps a PDO object, there's no need to sanitize, just
parameterize anything that may be unsafe in a prepared statement. The
retrieval functions will all return the data in a form that's easy to access
so you don't need to worry about getting an associative array when all you
want is a single cell or worry about getting a table when all you want is a
single row. This helps to reduces clutter in your code. Any PDOExceptions
are wrapped with the DBException class that implements an __toString method
that outputs a nice message to make error output clean and consistent.
The interface for you object does little more than replace the mysql_*
function calls with calls to an equivalent function in your oject.
Re-writing your example from above:
$db = DB::getInstance();
$value = 'value';
$db->prepare('SELECT * FROM wherever WHERE field=:val, 'getData');
$db->execute('getData', array(':val' => $value));
foreach($db->getResults('getData') AS $row) {
// Do stuff
}
Resulting in a reduction of 5 lines of code. I may not sound that impressive
for a small example but over the number of times you generally access a
database it will really add up.
--
Philip Graham
Lightbox Technologies
www.lightbox.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
