DbTable and all its glory

The example: http://www.koopman.me/dbtable/

I got the concept of the class, DbTable, from a book called PHP5 Professional. The idea is we have a class, this abstract class, that allows us to quickly make a new class out of any database table. Database tables make good objects. We often make them names of objects, like Shopper or Product. It makes sense to create classes that represent these objects, and to have a clean, consistent way to manipulate the data in the table. It also abstracts the database layer from application logic. If you just looked at example.phps, you’d have no idea if the database was flat file, MySQL, postgreSQL, or if it was even a database at all. Abstraction is a good thing, and one of the principles of object oriented programming.

So, you have this file, DbTable.php, that has an abstract class in it. You drop it into your project, and now whenever you want a new class that represents a database table, just make a new class that extends DbTable. It’s so simple, just a few lines of code, for example:


< ?php
$GLOBALS['primary_keys'][MY_DSN]['Shopper_Product'] = 'spid';
class Shopper_Product extends DbTable
{
  public function __construct( $id=null )
  {
     $this->initialize(MY_DSN, __CLASS__, $id);
  }
}
?>

and presto, you have your Shopper_Product class. Since we inherited from DbTable, we get all the powerful functions in DbTable. Of course, you can add methods to make the class do more. Shopper.phps and Product.phps class files have examples.

The DbTable class I got from the PHP5 Professional book was a good start. My version has some additional features:

  • Multiple database support
  • Connection caching
  • Primary key columns can have any name
  • Commented out section to set AUTOCOMMIT to 0
  • Added functions for commit, commitAll and rollback
  • Incorporated a simple Error class for error handling

I’ll come back later and write more on this topic. Its a great one, filled with many lessons. For example, accepting POST, then doing a header(“Location: …”) rather than letting the page load…. but that’s another story.

For reference, this is the database schema this example is working from is:


--
-- Table structure for table `Product`
--

CREATE TABLE `Product` (
  `pid` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `Shopper`
--

CREATE TABLE `Shopper` (
  `sid` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `Shopper_Product`
--

CREATE TABLE `Shopper_Product` (
  `spid` int(11) unsigned NOT NULL auto_increment,
  `sid` int(11) unsigned NOT NULL,
  `pid` int(11) unsigned NOT NULL,
  `add_date` datetime NOT NULL,
  PRIMARY KEY  (`spid`),
  UNIQUE KEY `sidpid` (`sid`,`pid`),
  KEY `sid` (`sid`),
  KEY `pid` (`pid`),
  CONSTRAINT `Shopper_Product_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `Product` (`pid`) ON DELETE CASCADE,
  CONSTRAINT `Shopper_Product_ibfk_2` FOREIGN KEY (`sid`) REFERENCES `Shopper` (`sid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

Comments are closed.