database class relations/inheritance logic questions

[Silverado_NL]

Hi. i have been making website's a while now using procedural programming and have been studying about object oriented programming for a while now.

i have been stuck trying to figure out a logical "database" class design or logical related classes that should be used by other object classes in order to store/retrieve information.(should be able to connect to database if it isnt already, and handle query's perform counts etc...)

what i would like from you guys is some pointers , to know if im heading in the right direction with this

or if this approach is just wrong.

and answer my probely very stupid questions below ???

currently im only using 1 mysql database, but i would like to leave options open for a possible extension or migration to oracle.

 

what im trying to understand is how this database class should be formed and how my other classes should interact with this database class.

for example (user related classes to retrieve or store userinfo, or forum_engine class to retrieve forum posts)

 

i realize calling mysql commands in others classes will make them dependant of mysql, so i created a basic database class that will perform the mysql query's for those other classes.

here's the basic code that i have so far.

<?php
class database{                         //connecing on construct might want to separate this from the other methods.
        protected $link_id;             //database connection variable
        protected $resource;          //query result variable
        
public function __construct($server,$user,$pass,$database,$prefix = ''){        
        // setup connection and select database,exit and display error if no mysql server host connection can be made OR if no database can be found
        if( !$this->link_id = @mysql_connect( $server, $user, $pass ) OR !mysql_select_db( $prefix.$database, $this->link_id )){
                exit(mysql_error().'  -  error number:'.mysql_errno());         
        }
}

public function runQuery($sql){
        $sql = $this->escapeString($sql);  //should stop SQL injection but calling the method here is probely wrong
        $this->resource = mysql_query($sql,$this->link_id) OR die(mysql_error());

}

public function numRows($sql = NULL){
        
        if($sql != NULL){
                $this->runQuery($sql);
        }
        return mysql_num_rows($this->resource);
        
}

function getArray($sql = NULL,$reset_pointer = false){
        
        if($sql != NULL){
            $this->runquery($sql);
        }
        // if data is already in resource, reset mysql pointer
        if($reset_pointer == true){
            mysql_data_seek( $this->resource, 0 );    
        }
        //puts arrays into multidimensional array
        while($array = mysql_fetch_array( $this->resource , MYSQL_ASSOC)){
           $multi_array[] = $array;
        }
        //return results if array is formed
        if(!is_array(@$multi_array)){
           die('Class: <u>'.__CLASS__ ."</u>    Function: <u>". __FUNCTION__ ."</u>, ERROR = function is unable to produce array from resource");
        }
        return $multi_array;
}

public function insertID($sql = NULL){
        
        if($sql != NULL){
            $this->runquery($sql);
        }    
        
        return mysql_insert_id($this->link_id);
}
    
public function affectedRows($sql = NULL){
            if($sql != NULL){
            $this->run_query( $sql );
            }
        return mysql_affected_rows($this->link_id);
}


private function escapeString($string){
        if(get_magic_quotes_gpc()){
                $string = stripslashes($string);
        }
return mysql_real_escape_string($string,$this->link_id);
}

public function fetchRow(){
}


public function __destruct(){
echo '<br>database_destruct';   
}
}

?>

 

Now my questions:

 

1.

if other classes have to use these database methods(either directly or through some database<->otherclass layer between them) what should i do?

 

should they be inherited by extending the other classes with the database class as parent(or maybe using a layer as parent, which in turn has the database class as parent )? not sure if that's good inheritance or not.

OR

should i pass an object instance of this database class into other classes? and not use inheritance?

 

 

2.

if lets say a user logs in and the login authentication class is called which in turn calls the database class for retrieval of login data

how do i avoid mysql connecting and disconnecting on each class that uses this database class.

i know the normal approach to checking mysql connection out of OO design, but how would this be done in classes? emptying the constructor would be the way, but how would i handle the database connecting?

 

what would be logical to do?

should i separate the actual connecting to the database that is currently done by the constructor from the rest of the class by creating a 2nd dbConnect class,also having to relate these classes to eachother and use my current database class as a db_toolbox class?(using db_toolbox cause figure of speech no idea what else to call it, maybe dbHandler or something)

 

since im only still using 1 database, should i make some sort of static connection function that ensure's im always connected? maybe a singleton pattern?

 

 

3.

another logic question is if the db_toolbox class is used should it check if there is a working database connection? and should it therefor inherit the connection variable from the dbConnect class??

 

and if there is a working database connection, how should i pass the $link_identifier?

through inheritance or passing into the toolbox class as a variable?

 

 

 

or do these question dont make much sence at ALL?

i know how classes can be extended etc, but have no idea how to put this in practical use.

for example should the db class be inherited at all? or should i use another way?

 

any advice would be greatly appreciated.

and sorry for my very long and messy way of explaining.

 

 

[DocSeuss]

1) I would create the database object once in my main code and pass a reference to it to any other classes they may need to use a db connection.

2) I would move the actual connecting of the database out of the contruct and put into a child function.

3) this would be a non issue.

 

Code may look something like this.

$mydbobject = new database($server,$user,$pass,$database,$prefix = ''); // create a database object.
$mydbobject->connect(); // connection member funtion inside database class only called once. 
$myuserobject = new user(); // create a user object.
$login = $user->login($name, $pass, $mydbojbect); // call member function login of user class and do login logic. In this example it would return some message.

 

There are many ways to go about it just try to think of a class as an object. There is nothing wrong with an object using another object. I'm sure you have read about the popular bicycle example when reading about OOP. Imagine if you also had a surface object, it could be a road, dirt trail, grass field, ect.. You wouldn't  have the bicycle extend the surface object in order to move on it.