OOP Mysql Function Class (Check Code)

[Phil.Roberts]

What does the '&' sign mean in your examples? I searched through the manual, the forums, and google and can't find anything.

http://uk.php.net/manual/en/language.references.php

[Clio]

Nice thread ... so, community, what will you say about this?

<?php

/*
+--------------------------------------------------------------------------
|   FlightDesign Internet Development Department
|   =========================================
|   by Cyrill Polikarpov
|   (c) 2002-2003 FlightDesign
|   [email]cyrill@f-design.com.ua[/email]
|   =========================================
|   Visit: [url]http://f-design.com.ua[/url]
|   Email: [email]cyrill@f-design.com.ua[/email]
|   Email: [email]clio@nix.zp.ua[/email]
|   ICQ #: 99885395
+---------------------------------------------------------------------------
|
|   > $$MYSQ.PHP
|
|    > Version: 1.0.5
+--------------------------------------------------------------------------
*/

class mysql {

    var $obj = array ( "sql_database"   => ""         ,
                       "sql_user"       => "root"     ,
                       "sql_pass"       => ""         ,
                       "sql_host"       => "localhost",
                       "sql_port"       => ""         ,
                       "persistent"     => "0"        ,
                       "sql_tbl_prefix" => "fd_"    ,
                       "cached_queries" => array()    ,
                     );
                     
     var $query_id      = "";
     var $connection_id = "";
     var $query_count   = 0;
     var $record_row    = array();
     var $return_die    = 0;
     var $error         = "";
                  
    /*========================================================================*/
    // Connect to the database                 
    /*========================================================================*/  
                   
    function connect() {
    
        if ($this->obj['persistent'])
        {
            $this->connection_id = mysql_pconnect( $this->obj['sql_host'] ,
                           $this->obj['sql_user'] ,
                           $this->obj['sql_pass'] 
                         );
        }
        else
        {
            $this->connection_id = mysql_connect( 
                                                  $this->obj['sql_host'] ,
                                         $this->obj['sql_user'] ,
                                  $this->obj['sql_pass'] 
                                );
        }
        
        if ( !mysql_select_db($this->obj['sql_database'], $this->connection_id) )
        {
            echo ("ERROR: Cannot find database ".$this->obj['sql_database']);
        }
    }
    
    
    
    /*========================================================================*/
    // Process a query
    /*========================================================================*/
    
    function query($the_query, $bypass=0) {
        
        //--------------------------------------
        // Change the table prefix if needed
        //--------------------------------------
        
        if ($bypass != 1)
        {
            if ($this->obj['sql_tbl_prefix'] != "fd_")
            {
               $the_query = preg_replace("/fd_(\S+?)([\s\.,]|$)/", $this->obj['sql_tbl_prefix']."\\1\\2", $the_query);
            }
        }
        
        $this->query_id = mysql_query($the_query, $this->connection_id);
      
        if (! $this->query_id )
        {
            $this->fatal_error("MySQL query error: $the_query");
        }
        
        $this->query_count++;
        
        $this->obj['cached_queries'][] = $the_query;
        
        return $this->query_id;
    }
    
    
    /*========================================================================*/
    // Fetch a row based on the last query
    /*========================================================================*/
    
    function fetch_row($query_id = "") {
    
        if ($query_id == "")
        {
            $query_id = $this->query_id;
        }
        
        $this->record_row = mysql_fetch_array($query_id, MYSQL_ASSOC);
        
        return $this->record_row;
        
    }

    /*========================================================================*/
    // Fetch the number of rows affected by the last query
    /*========================================================================*/
    
    function get_affected_rows() {
        return mysql_affected_rows($this->connection_id);
    }
    
    /*========================================================================*/
    // Fetch the number of rows in a result set
    /*========================================================================*/
    
    function get_num_rows() {
        return mysql_num_rows($this->query_id);
    }
    
    /*========================================================================*/
    // Return the amount of queries used
    /*========================================================================*/
    
    function get_query_cnt() {
        return $this->query_count;
    }
    
    /*========================================================================*/
    // Free the result set from mySQLs memory
    /*========================================================================*/
    
    function free_result($query_id="") {
    
           if ($query_id == "") {
            $query_id = $this->query_id;
        }
        
        @mysql_free_result($query_id);
    }
    
    /*========================================================================*/
    // Shut down the database
    /*========================================================================*/
    
    function close_db() { 
        return mysql_close($this->connection_id);
    }
    
    /*========================================================================*/
    // Basic error handler
    /*========================================================================*/
    
    function fatal_error($the_error) {
        global $INFO;
        
        
        // Are we simply returning the error?
        
        if ($this->return_die == 1)
        {
            $this->error = mysql_error();
            return TRUE;
        }
        
        $the_error .= "\n\nmySQL error: ".mysql_error()."\n";
        $the_error .= "mySQL error code: ".mysql_errno()."\n";
        $the_error .= "Date: ".date("l dS of F Y h:i:s A");
        
        $out = "<html><head><title>Database Error</title>
               <style>P,BODY{ font-family:arial,sans-serif; font-size:11px; }</style></head><body>
               &nbsp;<br><br><blockquote><b>There appears to be an error with the database.</b><br>
               You can try to refresh the page by clicking <a href=\"javascript:window.location=window.location;\">here</a>, if this
               does not fix the error, you can contact the site administrator by clicking <a href='mailto:{$INFO['EMAIL_IN']}?subject=SQL+Error'>here</a>
               <br><br><b>Error Returned</b><br>
               <form name='mysql'><textarea rows=\"15\" cols=\"60\">".htmlspecialchars($the_error)."</textarea></form><br>We apologise for any inconvenience</blockquote></body></html>";
               
    
        echo($out);
        die("");
    }
    
    /*========================================================================*/
    // Create an array from a multidimensional array returning formatted
    // strings ready to use in an INSERT query, saves having to manually format
    // the (INSERT INTO table) ('field', 'field', 'field') VALUES ('val', 'val')
    /*========================================================================*/
    
    function compile_db_insert_string($data) {
    
        $field_names  = "";
        $field_values = "";
        
        foreach ($data as $k => $v) {
            $v = preg_replace( "/'/", "\\'", $v );
            //$v = preg_replace( "/#/", "\\#", $v );
            $field_names  .= "$k,";
            $field_values .= "'$v',";
        }
        
        $field_names  = preg_replace( "/,$/" , "" , $field_names  );
        $field_values = preg_replace( "/,$/" , "" , $field_values );
        
        return array( 'FIELD_NAMES'  => $field_names,
                      'FIELD_VALUES' => $field_values,
                    );
    }
    
    /*========================================================================*/
    // Create an array from a multidimensional array returning a formatted
    // string ready to use in an UPDATE query, saves having to manually format
    // the FIELD='val', FIELD='val', FIELD='val'
    /*========================================================================*/
    
    function compile_db_update_string($data) {
        
        $return_string = "";
        
        foreach ($data as $k => $v) {
            $v = preg_replace( "/'/", "\\'", $v );
            $return_string .= $k . "='".$v."',";
        }
        
        $return_string = preg_replace( "/,$/" , "" , $return_string );
        
        return $return_string;
    }
    
} // end class


?>

[Mike Borozdin]

And what about separating connection class and result set class, like this:

<?
class Database
{
  function Database ( $Host, $User, $Password, $Database )
  {
  }
  function Query ( $Sql )
  {
  }
  function Close ()
  {
  }
}
class Query
{
  var $_Sql;
  var $_Res;
  var $_CurrentRow = 0;
  var $_Error;
  var $_IsSuccess = true;
  function Query ( $Database, $Sql )
  {
  }
  function _handleError ()
  {
     $this->_IsSuccess = false;
     $this->_Error = mysql_error ();
     print ( $this->getError () );
  }
  function getError ()
  {
     return $this->_Error . "<br />" . $this->_Sql . "<br />";
  }
  function getNumRows ()
  {
  }
  function setRow ()
  {
  }
  function getRow ()
  {
  }
}
//----------------------------------------------------------------------------------------------------------------
class MySql extends Database
{
  function MySql ( $Host, $User, $Password, $Database )
  {
     mysql_connect ( $Host, $User, $Password );
     mysql_select_db ( $Database );
  }
  function Query ( $Sql )
  {
     return new MySqlQuery ( $this, $Sql );
  }
}
class MySqlQuery extends Query
{
  function MySqlQuery ( $Database, $Sql )
  {
     $this->_Sql = $Sql;
     $this->_Res = mysql_query ( $Sql ) or $this->_handleError ();
  }
  function getNumRows ()
  {
     return mysql_num_rows ( $this->_Res );
  }
  function setRow ( $Row )
  {
     return mysql_data_seek ( $this->_Res, $Row );
  }
  function getRow ()
  {
     if ( $this->_CurrentRow == $this->getNumRows () ) {
        return false;
     }
     $row = mysql_fetch_array ( $this->_Res );
     $this->_CurrentRow++;
     return ( $row );
  }
  function Close ()
  {
     mysql_close ();
  }
}
?>

[Phil.Roberts]

Nice thread ... so, community, what will you say about this?

I say that if you're going to steal the code from Invisionboard and pass it off as your own then you should do so in a slightly more subtle manner.

[Clio]

I say that if you're going to steal the code from Invisionboard and pass it off as your own then you should do so in a slightly more subtle manner.

(((( Sorry, i parsed another file-header. Really, this is InvisionBoard class for MySQL database, but i have my own! database classes for PgSQL, MSSQL and mSQL for IBforums, which wasn't included in original IBforums package, 'cause i don't have MySQL on my hosting-server. Sorry for codesample - i had no intention to arrogate to myself somebody else's code, it's just an accidental mistake

[daemian]

I've got a question about ADODB. Since codezilla seems to like it. Maybe he or someone else can answer it.

Browsing some php projects installed on my system, which all use ADODB, I saw the following (to me, shocking) facts.

The oldest version of adodb on my system is 3.31 which came with phpPgAdmin 3.0rc1

Lines of code for adodb.inc.php: 625


The second oldest version on my system is 3.60 which comes with the most recent postnuke version 0.726

Lines of code for adodb.inc.php: 3354


The most recent version on my system is 4.01 which I downloaded standalone.

Lines of code for adodb.inc.php: 3629

These lines don't include the lines of code for the adodb database drivers or other files which it might include.

My questions are:
- What are the reasons for this dramatic increase of code?
- Has anyone noticed significant increase of overhead / execution time over this evolution?
- if yes, does it pay (solved bugs/or usefull features) to use newer versions over older ones?

[Phil.Roberts]

ADODb contains a very large amount of code designed to emulate various DB-specific functions in order to maintain cross-db compatibility. Which seems a bit silly to me as I don't think anyone in their right mind would rely on emulation of DB features rather than just coding for the DB should they ever migrate from one DB type to another.

[codezilla]

Phil.Roberts, I used to think that way until I had a project that needed to work on Oracle. The problem was that the machine I was developing on did not have Oracle, and since it's extremely expensive my only option was to develop on one DB (MySQL) and deploy on another (Oracle). Using a DB abstraction layer made the deployment process very easy.

[Phil.Roberts]

Personally if I were even in that situation I'd prefer to abstrate out the database calls altogether using the DAO pattern rather than mess about with re-writing proprietary SQL on the fly....

[codezilla]

It's not about re-writing proprietary SQL, it's about not using database-dependent function calls. Essentially, it's like using DBX instead of having a set of MySQL code and a separate set of Oracle code.

[daemian]

This discussion about ADODB being usefull or not was not really my intention.

I know there are people who think it's bloated. Although other people prefer it over PEAR::DB or phplib.

The point to my questions is:

What do 3000 lines in only a half year contribute to an already fully functional library of approximately 600 lines of code?
Of course this isn't really true, because I'm only talking about the main file. The only reason I can think of is that a lot of code from the other files was moved to the main file.

[codezilla]

I really don't know much about the specifics of ADOdb. I just know that it works well for my needs. Sorry, I can't help you any more than that.

[wogboy]

Between the versions of ADODB mentioned the RecordSet class and Connection classes were broken out into seperate files and then later merged back in again into the main ADODB file.

I think John Lim (ADODB author) determined that having separate files decreased performance.

The code size of the core library remained much the same. New optional features (like the awesome query profiling) have been added more recently as well, but they only appear in the main library file as function stubs for the most part, in order load the rest of the code.

[moonchild]

Here is my MySQL class, i just finished it yesterday:

// Class:    MySQL 
    class MySQL { 
        /* Initialize/Define Class Variables */ 
            var $dbh; 
            var $dbs; 
            var $dba; 
            var $query; 
            var $result; 
            var $numrows; 
            var $row = array(); 
        // Function: MySQL() 
            function MySQL() { 
                $this->dbh = mysql_connect(MYSQL_SERVER,MYSQL_UID,MYSQL_PWD) or die(mysql_error()); 
            } 
        // Function: Select_DB() 
            function Select_DB() { 
                if($this->dba != NULL) { 
                    $this->dbs = mysql_select_db($this->dba,$this->dbh) or die(mysql_error()); 
                } else { 
                    $this->dbs = mysql_select_db(MYSQL_DBA,$this->dbh) or die(mysql_error()); 
                } 
            } 
        // Function: Disconnect() 
            function Disconnect() { 
                mysql_close($this->dbh); 
            } 
        // Function: Flush() 
            function Flush() { 
                mysql_free_result($this->result); 
            } 
        // Function: NumRows() 
            function NumRows() { 
                return $this->numrows = mysql_num_rows($this->result); 
            } 
        // Function: Query() 
            function Query() { 
                $this->result = mysql_query($this->query) or die(mysql_error()); 
            } 
        // Function: FetchArray() 
            function FetchArray() { 
                return mysql_fetch_array($this->result); 
            } 
        // Function: FetchRow() 
            function FetchRow() { 
                return mysql_fetch_row($this->result); 
            } 
    } 

[moonchild]

i would have never thought of doing:

return MysqlStatment($sql,$this); 

and using

$this->connection->getConnectionId(); 

This post has helped me a bunch, unfortunately now I'm trying to figure out how to rewrite all of mysql classes. heh

[moonchild]

What does the '&' sign mean in your examples? I searched through the manual, the forums, and google and can't find anything.

http://us4.php.net/language.variables ... it took me a while to find it as well.

[haaX]

// Previously MysqlConnectClass()
class MysqlConnection
{

   (...)

    function MysqlConnection($host, $database, $username, $password)
    {
        $this->host = $host;
        $this->database = $database;
        $this->username = $username;
        $this->password = $password;
        $this->open();
    }

    (...)

} 

--

class Sillysoft_MysqlConnection extends MysqlConnection
{

    var $dblocation = '*****';
    var $dbname = '****';
    var $dbpass = '**';
    var $dbuser = '***';

    function Sillysoft_MysqlConnection()
    {
        $this->MysqlConnection($dblocation, $dbname, $dbuser, $dbpass);
    }

} 

My question is concerning the constructors. In java you would want to do a this.super(args) to call the superclass (the class the current class extends from) -- there arent something equal in php? I think I've seen parent:: somewhere?

Secondly, I wonder what the & infront of functions is - is it simply that returns are passed by reference?

Great post btw, I'm currently writning my first large OOP application in PHP. Those tips on OOP thinking here was also very good - it's important to think of that when you're stuck in the middle of the coding having a few problems. I've been there a few times, making one big class with many tasks. Bad OOP, but it does the job(...not as efficent as you may want, I believe).

Concerning performance -- I've heard that OOP is slower than non-OOP in PHP; what makes it slower, and how to reduce this performance loss?


Regards,
Haakon

[haaX]

I almost forgot.. How to integrate error handling in these classes (from page one)? Do you implement it as one external class for errors, or do you incorportate them into the exsisting classes?

Regards,
Haakon

[Daimaju]

class MysqlError
{
    var $statement;
    var $file;
    var $line;
    var $error;
    
    function MysqlError($statement, $file, $line) {
        $this->statement = & $statement;
        $this->file = $file;
        $this->line = $line;
        $this->CreateError();
        $this->DisplayError();

    }
    
    function CreateError()
    {
        $this->error = 'MysqlError: '. mysql_error($this->statement->connection->getConnectionId()).'<br />';
        $this->error .= 'Statement: '.$this->statement->getPreparedSql().'<br />';
        $this->error .= 'File: '.$this->file.'<br />';
        $this->error .= 'Line: '.$this->line.'<br />';
    }
    
    function DisplayError()
    {
        echo $this->error;
        exit;
    }
} 

Just created after i read this excellent Thread

I used Codezilla's Connection, Statement and ResultSet Classes, only change was in MysqlStatement:

function &execute($file = '', $line = '')
    {
        if($resultId = mysql_query($this->getPreparedSql(), $this->connection->getConnectionId())) {
            return new MysqlResultSet($resultId);
        }else{
    return new MysqlError($this, $file, $line);
        };
    } 

And now i better wait for critics on my class befor using it

Edit:


One idea i just got:
What about letting DisplayError decide wether (how is it spelled?) to exit/die the script, or continue with only a Warning/Notice?
I always let the script die when an sql-error occurs

Another Point:
My Error class only handles mysql_query errors, but if an error during the connection occurs (bad password, user or database) i don't want to have the mysql_error text but one like: "Could not connect to Server" or "Could not select Database" How do i solve this the best way?

[Daimaju]

I've kept working on it

class MysqlError
{
    var $statement;
    var $file;
    var $line;
    var $error;
    var $errortype;
    
    function MysqlError($statement, $file, $line, $type = 'statement') {
        $this->statement = & $statement;
        $this->file = $file;
        $this->line = $line;
        $this->errortype = $type;
        $this->CreateError();
        $this->DisplayError();

    }
    
    function CreateError()
    {
        switch ($this->errortype) {
            case 'statement':
                $this->error = 'MysqlError: '. mysql_error($this->statement->connection->getConnectionId()).'<br />';
                $this->error .= 'Statement: '.$this->statement->getPreparedSql().'<br />';
                $this->error .= 'File: '.$this->file.'<br />';
                $this->error .= 'Line: '.$this->line.'<br />';
                break;
            case 'connect':
                $this->error = 'An error occured during connection to the server!<br />';
                break;
            case 'select':
                $this->error = 'An error occured selecting the database!<br />';
                break;
        }
    }
    
    function DisplayError()
    {
        echo $this->error;
        exit;
    }
} 

Thus changed MysqlConnection:

function open()
    {
        if($this->connectionId = @mysql_connect($this->host, $this->username, $this->password)) {
            if(!@mysql_select_db($this->database, $this->connectionId)) {
                return new MysqlError('', '', '', 'select');
            };
        }else{
            return new MysqlError('', '', '', 'connect');
        }
    } 

But i'm not really happy with it, because of the empty paramters in MysqlError, i think i should change the order.

But i want $type to be the last param, because you usually connect once to the server and query it more than once.
Any suggestions how to solve this?

[lazy_yogi]

Actually, prepared statements are one of the main benefits of a Statement (or more accurately, PreparedStatement, which extends Statement) class. I didn't bother getting into all that because MySQL doesn't support them

Prepared statements are only of use if the database implements it. The purpose of it is so that if the prepared statement is correct, each time you run the query, the db does not have to parse the sql because all you're passing in is the params for the query - which doesn't need to be checked for syntax (except when creating the prepared statement in the first place). While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.

If you examine the database classes in the Eclipse library you'll see that they work in this exact same way.

Yes it does, and IMO eclipse's db abstraction is pretty damn close to ideal. I'd say just use that. But Geoff does explain the details very nicely.

Maybe UML should be taught in primary schools...

Lol ... interesting idea :-P

[moonchild]

While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.

I disagree. Look at the MysqlStatement class again.

[Webservant Alec]

Prepared statements are only of use if the database implements it. The purpose of it is so that if the prepared statement is correct, each time you run the query, the db does not have to parse the sql because all you're passing in is the params for the query - which doesn't need to be checked for syntax (except when creating the prepared statement in the first place). While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.

In the interest of completeness: prepared statements are supported by MySQL 4.1 (maybe 4.0?), though you can only use them from PHP with PHP 5's mysqli extension. But I agree, there isn't any advantage from a speed standpoint to emulating prepared statements in PHP 4. In fact, it's probably slower, right?

[haaX]

Like.... prepared statements? Now, what good do they do?

http://dev.mysql.com/doc/mysql/en/C_...tatements.html ... however, I dont think I'm quite getting the points. Its quicker when you want to do many of the same query? Now when would you do that?

I got really confused by this.

D'oh.

Regards,
Haakon

[Aesthetic-Theory]

You have the right idea -- but there are a lot of improvements that can be made.

First, think about the class itself (MysqlConnectClass) and what its purpose is.

The first thing I notice (and this is a relatively minor point) is that you have the word 'Class' in the name of your class. There's nothing wrong with that if you do that to all your classes as a naming convention, but if you really want to be thinking in terms of OO, everything will be a class so there's no point in typing the extra 5 characters.

Furthermore, it's a widely accepted convention to reserve [initial] capitalization for the names of your classes only -- nothing else (so I also recommend lower-casing the first letter of your method names). Naturally, as with any convention, not everyone agrees, but you certainly can't go wrong with it.

Ok, so what does that leave us with? MysqlConnect -- which I would rename to MysqlConnection because class names should, generally, be nouns since classes represent 'things'.

Now that we've refined the name, we need to think about what the purpose of a MysqlConnection class is. That's fairly obvious, the purpose is to handle a connection to a mysql database -- nothing more, nothing less. Keep in mind that "in most cases it is better to build small objects with specific task than to build large objects with many tasks" [p.11 "The Object-Oriented Thought Process" by Matt Weisfeld].

Next, think about what kinds of behaviors/actions a MysqlConnection object needs to be able to do in order to "handle a connection to a mysql database". These behaviors will become your methods and should be named with verbs since they represent actions. There are two obvious ones: open and close. But is there anything else that a MysqlConnection should do? Why have a MysqlConnection in first place? To execute a SQL statement and possibly do something with the result set. Now, re-read the last sentence. Notice anything? There were two verbs, and one in particular is catching my eye: execute (the other is 'do' but I'll address that later; in fact there were also two nouns: statement and result -- which I'll mention shortly).

Should we add an 'execute' method to MysqlConnection? Some people would say yes. Personally, I disagree with this -- but it's up for debate so I'll explain my thinking. What do you 'execute'? I already answered this: a statement. Which is a noun and therefore a good candidate for a class. What do you do with a statement? You execute it. So let's say we choose to create a Statement class and place the execute method on it (technically it's a MysqlStatement, but I'll use Statement for brevity). What else do you do with a statement? Not much, which is why some people choose not to implement a separate Statement class -- it doesn't do enough to make it worthwhile. Generally speaking, if a class only has one method, then it probably shouldn't be a class.

But, a Statement class is useful for than just executing SQL statements. Think about it for a second. What else do you frequently do with SQL statements other than executing them? You probably put the value of a variable in your statements all them time (e.g. "SELECT * FROM table WHERE id = $id"). A lot of times those variables contain data that came from a form submission by an end-user -- which means you can't trust it. That's why we have functions like mysql_escape_string() (which is very similar to addslashes()). Wouldn't it be great if the Statement class was responsible for doing things like addslashes() so you don't have to worry about it? I definitely think so. In fact, I can't think of a better place to put that kind of functionality. For that reason, I think that a Statement class is very worthwhile.

If we're going to have a separate Statement class with an execute method (instead of putting execute on MysqlConnection), how do we create a statement? See that verb? Why not put a create() method on your MysqlConnection class? Well, we should probably call it createStatement() so its purpose is a little clearer. Sounds good to me. Then we can be done with MysqlConnection, right? Not so fast.

We have to talk about how MysqlConnection gets its job done. What data does it need to open() a connection to a database? The name of the database server (host), the name of the database, a username, and password. And when we open() the connection we need to store the resource id, so we can later close() the connection (which reminds me, let's also add a getConnectionId() method, so our Statement objects will be able use the connection resource id when execute()'ing sql statements). Guess what, we just figured out all our properties: host, database name, username, password, and resource ID (you had 4 out of 5, Sillysoft). NOW we're done with MysqlConnection, right? Nope, no quite, but almost.

We need to figure out how a MysqlConnection will get access to the data that those properties store. Sillysoft, you hard coded them in the class, which is okay unless you want the class to be reusable. I would recommend passing them to MysqlConnection when you instantiate the class. That way you could connect to multiple mysql databases using the same class. If you want them hard-coded define some constants in a convenient file that always gets include()'d so you don't have to hunt for them.

Here's what we have so far (without error handling to keep things simple):

// Previously MysqlConnectClass()
class MysqlConnection
{

    var $connectionId;
    var $database; // Previously $dbname
    var $host;     // Previously $dblocation
    var $password; // Previously $dbpass
    var $username; // Previously $dbuser

    function MysqlConnection($host, $database, $username, $password)
    {
        $this->host = $host;
        $this->database = $database;
        $this->username = $username;
        $this->password = $password;
        $this->open();
    }

    // Previously CloseMysqlConn()
    function close()
    {
        $this->connectionId = mysql_close($this->connectionId);
    }

    function &createStatement($sql)
    {
        return new MysqlStatement($sql, $this);
    }

    function getConnectionId()
    {
        return $this->connectionId;
    }

    function open()
    {
        $this->connectionId = mysql_connect($this->host, $this->username, $this->password);
        mysql_select_db($this->database, $this->connectionId);
    }

} 

Now if you still want to hard code the connection parameters in a class you can easily extend the base class:

class Sillysoft_MysqlConnection extends MysqlConnection
{

    var $dblocation = '*****';
    var $dbname = '****';
    var $dbpass = '**';
    var $dbuser = '***';

    function Sillysoft_MysqlConnection()
    {
        $this->MysqlConnection($dblocation, $dbname, $dbuser, $dbpass);
    }

} 

Now let's flesh out the Statement class. We want to be able to pass parameters to the Statement and have it automatically put quotes around it and make sure the string is escaped. Then, when we execute the Statement, we want it to return a ResultSet (actually a MysqlResultSet), which is the other noun/class I mentioned a while ago. Like so:

class MysqlStatement
{

    var $connection;
    var $parameters;
    var $sql;

    function MysqlStatement($sql, &$connection)
    {
        $this->connection =& $connection;
        $this->parameters = array();
        $this->sql = $sql;
    }
    
    function &execute()
    {
        $resultId = mysql_query($this->getPreparedSql(), $this->connection->getConnectionId());
        return new MysqlResultSet($resultId);
    }
    
    function getPreparedSql()
    {
        $sql_parts = explode('?', $this->sql);
        $sql = $sql_parts[0];
        for ($i = 1, $max = count($sql_parts); $i < $max; $i++)
        {
            $sql .=  $this->parameters[$i] . $sql_parts[$i];
        }
        return $sql;
    }
    
    function setParameter($index, $val)
    {
        $this->parameters[$index] = "'" . mysql_escape_string($val) . "'";
    }

}

/*
// Usage:
$conn =& new MysqlConnection('localhost', 'my_db', 'my_user', 'my_pass');
$stmt =& $conn->createStatement(
    'SELECT last_login FROM user WHERE username = ? AND password = ?');
$stmt->setParameter(1, $username);
$stmt->setParameter(2, $password);
$rs =& $stmt->execute();
*/ 

The ResultSet class is pretty simple too. What behaviors should it have? As a minimum, you should be able to iterate through the rows, get the total number of rows, get the fields within a row, and close/release/free the ResultSet. There may be other stuff that would be useful, but I'm keeping this example bare bones. Take a look:

class MysqlResultSet
{

    var $record;
    var $resultId;

    function MysqlResultSet($resultId)
    {
        $this->record = array();
        $this->resultId = $resultId;
    }

    // Previously FreeMysqlMem()
    function close()
    {
        mysql_free_result($this->resultId);
        $this->record = array();
        $this->resultId = NULL;
    }

    function getField($field)
    {
        return $this->record[$field];
    }

    // previously GetNumRows()
    function getRowCount() 
    {
        return mysql_num_rows($this->resultId);
    }

    // previously GetRowInfo() - sort of
    function next()
    {
        $this->record = mysql_fetch_assoc($this->resultId);
        return ($this->record !== FALSE);
    }

} 

Putting it all together, here's sample usage of all three classes.

$conn =& new MysqlConnection('localhost', 'my_db', 'my_user', 'my_pass');
$stmt =& $conn->createStatement(
    'SELECT model FROM car WHERE date >= ? AND date <= ? ORDER BY model ASC');
$stmt->setParameter(1, $start_date);
$stmt->setParameter(2, $end_date);
$rs =& $stmt->execute();
while ($rs->next())
{
    echo $rs->getField('model') . '<br>';
}
$rs->close(); //optional
$conn->close(); //optional 

These classes are seriously lacking in the way of error handling, but otherwise they work as is. And you can easily refactor them into a full-blown database abstraction layer, even if it only works with MySQL for now.

Keep in mind that everyone has there own opinions about what the best way of doing OOP is. For example, if you look at the PEAR database abstraction classes you'll see they have a much different approach than I (granted they do a lot more too -- but are very bloated). As you learn more and more about OOP you'll find better ways of doing things.

I don't know of any good articles off the top of my head. The vast majority of PHP specific articles, especially those dealing with OOP, are of very low quality. I wouln't look to them for examples of good OOP, I recommend looking to the pros for advice. In fact, when I was developing these classes originally, my main reference was the Java API docs (http://java.sun.com/j2se/1.4.1/docs/api/). Even if you don't know Java (I don't), they're really informative. Of course Java != PHP, but there's a lot to learn by seeing how they do things in Java. Check out the java.sql package, in particular the Connection, PreparedStatement, and ResultSet interfaces. The classes I presented above are basically simplfied versions of them. I refer to those docs all time when I am working on my classes.

A really great book for learning the foundations of OOP is the book I quoted above, "The Object-Oriented Thought Process" by Matt Weisfeld (ISBN: 0672318539). It's only about 200 pages so it's concise, but very helpful. There's very little code -- it's all about the concepts. I highly recommend it.

Another fantastic book, especially if you're trying to make the transition from procedural to OO, is Refactoring by Martin Fowler. It's considered a classic (and has been mentioned on these forums many times).

Well, this post is a tad longer then I thought it would be when I started. Hope it all makes sense and helps you.

-geoff

Hi,

First, that was an amazing post. I'm changing my DB class to reflect some of the things you said... although, I have one question.

Wouldn't using sprintf in getPreparedStatement be more efficient then the explode/loop method? I think it achieves basically the same... or is it wrong for this purpose? Please help me to udnerstand which method would be better.

Thanks,
Phil