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