Object Oriented design using a database.

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I wanted to know if is a good idea to create classes that mirror
exactly the structure of a database. This might sound a bit fuzzy but
I'l explain myself with an example:

CREATE TABLE `pl_cities` (
`city_id` int(10) NOT NULL auto_increment,
`city_name` varchar(16) NOT NULL default '',
`city_coordinates` varchar(10) NOT NULL default '',
`city_country` enum('germany','switzerlan','austria') NOT NULL
default 'germany',
PRIMARY KEY  (`city_id`)

This is the stuff I have in my database.
Now I make the following class (not really, I made a super-class which
handles the database interaction, but we'll assume my class is this

class City{
var $vars = array("id" => 0);
var $tableprefix = "city_";
var $tablename = "pl_cities";
var $errorMsg = "No Error";

* Constructor loads the default data into the variables.
function PlanerClass(){
foreach($this->vars as $key => $value){
$this->$key = $this->vars[$key];

* Unserialize the object from
function unserial($inst){
//$inst = unserialize($inst);
$data = get_object_vars($inst);
foreach($this->vars as $key => $value){
//print($key." => ".$value." => ".$data[$key]."<br>\n");
$this->$key = $value;
$this->$key = $data[$key];
return count($this->vars);

function doprint(){
foreach($this->vars as $key => $value){
print($key." => ".$this->$key."<br>\n");

function load($o_id){
global $mysql;
$sql = "SELECT * FROM ".$this->tablename." WHERE ";
$sql .=$this->tableprefix."id='".$o_id."' ";
$sql .= "OR ".$this->tableprefix."name='".$o_id."' ";
$sql .="LIMIT 1";
$data = $mysql->FetchArray();

function inject($data){
foreach($this->vars as $key => $value){
$this->$key = $data[$this->tableprefix.$key];
function save(){
global $mysql;
$sql = "UPDATE ".$this->tablename." SET ";
$splits = array();
foreach($this->vars as $key => $value)
$splits[] =
$sql .= implode(", ",$splits);
$sql .= " WHERE ".$this->tableprefix."id='".$this->id."' LIMIT 1;";
return $mysql->RawQuery($sql);

At the top I have an array of variable-names which are loaded from the
database if an ID has been specified. As you might already have guessed
I generate alot of SQL-Queries by using these classes (even if I do use
City::inject() which is used to load a recordset). Is it a good idea?

Re: Object Oriented design using a database.

Quoted text here. Click to load it

I don't know about mirroring "exactly." I do something very similiar, but
the classes are structured with ease of programming in mind. Records from
multiple tables are placed into one object when they belong together
conceptually. For example, I use a UserProfile class to hold all the info
about a user.

Re: Object Oriented design using a database.

I don't see why not, for the simple reason that this is exactly what I do.
Take a look at  http://www.tonymarston.co.uk/php-mysql/databaseobjects.html .
I have actually built a full-blown infrastructure using this technique which
is described at http://www.tonymarston.co.uk/php-mysql/infrastructure.html
with a small sample application described at
http://www.tonymarston.co.uk/php-mysql/sample-application.htmlwhich can be
run online or you can download all the code.

I must warn you that there are some OO aficionados who think that creating a
separate class for each database table is OOO (out of order). Their
criticisms and my replies are documented at


Tony Marston


Quoted text here. Click to load it

Site Timeline