SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data Access Classes.

    Ok following on from my thread yesterday today I've set out to write a data access object which I've succeed in doing.

    Basically the class simply handles the mysql_connect and mysql_close commands and should I need to add another db I can simply extend a new class of my database class superclass.

    My problem now comes when thinking about doing other tasks like calculating num_rows, selects, inserts, updates. Do I simply include add more methods in to the superclass to deal with these requirements or should they be in a rule based class??

    Any code snippets would once again be greatly appreciated!!!

  2. #2
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by furious5
    Any code snippets would once again be greatly appreciated!!!
    You show me yours and I'll show you mine .

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mine is very simple and currently looks like this:

    Code:
    class DataBase {
    
    var $host;
    var $dbname;
    var $username;
    var $password;
    
    // Constructor
    function connect() { }
    function disconnect() {} 
    
    }
    
    class MySQL extends DataBase {
    
    // Constructor
    function MySQL() {
    	$this->host = 'localhost';
    	$this->dbname = 'db_name';
    	$this->username = 'username';
    	$this->password = 'password';
    }
    
    // Accessor
    function connect(){
    	$this->link = mysql_connect($this->host,$this->username,$this->password) or die(mysql_error());
        return mysql_select_db($this->dbname, $this->link) or die(mysql_error()); 
    }
    
    // Accessor
    function disconnect() {
    	mysql_close($this->link);
    } 
    
    }
    I had also thought about adding methods such as numrows() and query() within this class, but I'm not sure whether this would be a good solution or whether in fact there should be further classes for these.

    My problem with this, would be how I would keep my db link alive.

  4. #4
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fwiw, here's a stripped-down version of what we have previously used in PHP5 - I've omitted the docblocks and whatnot for the sake of brevity:

    PHP Code:
    <?php

    class MySQLDatabase implements Database {

        private 
    $host;
        private 
    $user;
        private 
    $pass;
        private 
    $name;

        private 
    $link;

        private 
    $last_insert_id;
        private 
    $num_affected;    

        public function 
    __construct($host$user$pass$name)
        {
            
    $this->host $host;
            
    $this->user $user;
            
    $this->pass $pass;
            
    $this->name $name;
        }

        public function 
    connect()
        {
            if (
    $this->link mysql_connect($this->host$this->user$this->pass)) {

                if (
    mysql_select_db($this->name$this->link)) {
                    return 
    TRUE;
                }
            }
            throw new 
    DatabaseException(mysql_error($this->link));
            return 
    FALSE;
        }

        public function 
    query($sql)
        {
            if (
    $result = @mysql_query($sql$this->link)) {
                return new 
    MySQLRecordset($result$this->link);
            } else {
                throw new 
    DatabaseException(mysql_error($this->link));    
                return 
    FALSE;
            }
        }

        public function 
    insert($sql)
        {
            if (@
    mysql_query($sql$this->link)) {
                
    $this->last_insert_id mysql_insert_id($this->link);
                return 
    TRUE;
            } else {
                throw new 
    DatabaseException(mysql_error($this->link));    
                return 
    FALSE;
            }
        }

        public function 
    getInsertID()
        {
            return 
    $this->last_insert_id;
        }

        public function 
    update($sql)
        {
            if (@
    mysql_query($sql)) {
                
    $this->affected mysql_affected_rows($this->link);
                return 
    TRUE;
            } else {
                throw new 
    DatabaseException(mysql_error($this->link));    
                return 
    FALSE;
            }
        }

        public function 
    getNumAffected()
        {
            return 
    $this->num_affected;
        }

        public function 
    escape($param)
        {
            if (
    is_array($param)) {
                while (list(
    $key$val) = each($param)) {
                    
    $this->escape($param[$key]);
                }
                
    reset($param);
            } else {
                
    $param mysql_real_escape_string($param$this->link);
            }
            return 
    $param;
        }

        public function 
    __destruct()
        {    
            if (
    $this->link != null) {
                @
    mysql_close($this->link); 
                unset(
    $this->link);        
            }
        }
    }

    ?>
    and so the link is simply stored as a private member of the class. We don't have the usernames/passwords stored in there, so that we can connect to more than one DB. I guess the other useful point is that we wrap a resultset in another class which gives us things like the number of rows in the result set etc. Again, stripped of its worldly baggage:

    PHP Code:
    <?php

    class MySQLRecordset implements Recordset {
        
        private 
    $resource;
        private 
    $parentlink;

        public function 
    __construct(&$res, &$link)
        {
            
    $this->resource   =& $res;
            
    $this->parentlink =& $link
        }

        public function 
    numRows()
        {
            return 
    mysql_num_rows($this->resource);
        }

        public function 
    fetchRow()
        {
            if (
    $row mysql_fetch_assoc($this->resource)) {
                return 
    $row;
            } else {
                return 
    FALSE;
            }                
        }

        public function 
    findRow($rownum)
        {
            if (
    mysql_data_seek($this->resource$rownum)) {
                return 
    TRUE;
            } else {
                throw new 
    DatabaseException(mysql_error($this->parentlink));    
                return 
    FALSE;
            }
        }
    }

    ?>
    Not claiming it's perfect, and I don't imagine for a minute that it gives us a shred of real abstraction (the data mapper layer hopefully does that) but it seems to provide a modest amount of convenience at least.

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for this, that seems a much better way of doing things.

    Having had a quick look through the code, I have just one question.

    On performing a query, how do you then access the MySQLRecordSet Object that is returned? If I was to call the object like I have below, how would I be able to loop through the MySQLRecordSet object???

    Code:
    $db = new MySQLDatabase($host, $db, $user, $pass);
    $db->connect();
    $db->query($sql);

  6. #6
    Resident Java Hater
    Join Date
    Jul 2004
    Location
    Gerodieville Central, UK
    Posts
    446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by furious5
    Thanks for this, that seems a much better way of doing things.

    Having had a quick look through the code, I have just one question.

    On performing a query, how do you then access the MySQLRecordSet Object that is returned? If I was to call the object like I have below, how would I be able to loop through the MySQLRecordSet object???

    Code:
    $db = new MySQLDatabase($host, $db, $user, $pass);
    $db->connect();
    $db->query($sql);
    Well in PHP5, iterators and use of the SPL ArrayAccess interface can be used to make interfacting recordsets easy. Creole uses iterators, which is very handy. The ArrayAccess interface can be used to access fields in each row.

    But you are using PHP4 it seems, so you can't do this.

  7. #7
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by furious5
    On performing a query, how do you then access the MySQLRecordSet Object that is returned? If I was to call the object like I have below, how would I be able to loop through the MySQLRecordSet object???
    That's simple enough in fact - the MySQLDatabase::query() function returns the Recordset object. That looks a bit like:

    PHP Code:
    <?php

    $resultset 
    $DB->query($sql);

    while (
    $array $resultset->fetchRow()) {
        echo 
    $array['column_name']; // or whatever...
    }

    ?>
    I definitely like the idea of our MySQLRecordset implementing Iterator...then the client code would not need to know whether it has getNextRow() or fetchNextRow() or whatever...food for thought

  8. #8
    SitePoint Guru
    Join Date
    Dec 2003
    Location
    oz
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been using MS Sql server in .Net for a while now, and the resultset is connected to the connection you have.

    So if you do this:
    DBResultIterator result = data_access.Query("some query");

    Then you can't execute another query until that query reult has been iterated through and the result set is closed. So you need a new connection for each query or stored proceedure.

    I'm thinking that to map that into a wrapper I should combing the result and connection into one object rather than break it up since thats what .Net does and it is actually used like that. So I'd use it like this:

    PHP Code:
    // gets a new object that has the connection established
    DataAccess da DAFactory.CreateDA(); 
     
    da.AddInputParam(@param”value);
    da.ExecuteProceedure(“proc_name”);
     
    while (
    da.MoveNext())
    {
        
    // use it like you sue a result iterator object
        // eg. da.GetCurrent()

    Any thoughts?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •