Object Oriented design using a database.

[Christian Decker]

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:

[sql]
CREATE TABLE `pl_cities` (
`city_id` int(10) NOT NULL auto_increment,
`city_name` varchar(16) NOT NULL default '',
`city_coordinat es` varchar(10) NOT NULL default '',
`city_country` enum('germany', 'switzerlan','a ustria') NOT NULL
default 'germany',
PRIMARY KEY (`city_id`)
) TYPE=MyISAM AUTO_INCREMENT= 1 ;
[/sql]

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
one):

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");
if(empty($data[$key])){
$this->$key = $value;
}else{
$this->$key = $data[$key];
}
}
return count($this->vars);
}

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

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

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[] =
$this->tableprefix.$key."='".addslashes($this->$key)."'";
$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?
Snyke

[Tony Marston]

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-mys...seobjects.html.
I have actually built a full-blown infrastructure using this technique which
is described at http://www.tonymarston.co.uk/php-mys...structure.html
with a small sample application described at
http://www.tonymarston.co.uk/php-mys...tion.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

http://www.tonymarston.co.uk/php-mysql/good-bad-oop.html

HATH.

--
Tony Marston

http://www.tonymarston.net

"Christian Decker" <Decker.Christi an@gmail.com> wrote in message
news:cia7a3$qvc @odak26.prod.go ogle.com...[color=blue]
>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:
>
> [sql]
> CREATE TABLE `pl_cities` (
> `city_id` int(10) NOT NULL auto_increment,
> `city_name` varchar(16) NOT NULL default '',
> `city_coordinat es` varchar(10) NOT NULL default '',
> `city_country` enum('germany', 'switzerlan','a ustria') NOT NULL
> default 'germany',
> PRIMARY KEY (`city_id`)
> ) TYPE=MyISAM AUTO_INCREMENT= 1 ;
> [/sql]
>
> 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
> one):
>
>

> 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");
> if(empty($data[$key])){
> $this->$key = $value;
> }else{
> $this->$key = $data[$key];
> }
> }
> return count($this->vars);
> }
>
> function doprint(){
> print("<p><b>".$this->tablename."</b><br>\n");
> foreach($this->vars as $key => $value){
> print($key." => ".$this->$key."<br>\n");
> }
> print("</p>\n\n");
> }
>
> function load($o_id){
> global $mysql;
> $sql = "SELECT * FROM ".$this->tablename." WHERE ";
> $sql .=$this->tableprefix."id='".$o_id."' ";
> if(array_key_exists("name",$this->vars))
> $sql .= "OR ".$this->tableprefix."name='".$o_id."' ";
> $sql .="LIMIT 1";
> $mysql->RawQuery($sql);
> $data = $mysql->FetchArray();
> $this->inject($data);
> }
>
> 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[] =
> $this->tableprefix.$key."='".addslashes($this->$key)."'";
> $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?
> Snyke
>[/color]

[Chung Leong]

Re: Object Oriented design using a database.

"Christian Decker" <Decker.Christi an@gmail.com> wrote in message
news:cia7a3$qvc @odak26.prod.go ogle.com...[color=blue]
> 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:[/color]

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.