Mark’s Site

I try to use a central database class in my code, simply because if I ever need to migrate from MySQL to another database engine (such as Microsoft SQL or Postgres), all I have to do is alter the one database class file and my application is all converted. Since everything goes through the database class, it’s the only file you need to change in such an instance.

The class code is as follows …see comments for details:

<?php
# Mark's modified MySQL class (adapted from Sitepoint PHP books from Harry Fuecks - thanks Harry - awesome read and advise)
class MySQL {
	#standard connection vars
    var $host;
    var $dbUser;
    var $dbPass;
    var $dbName;

	#MySQL Resource link identifier stored here
    var $dbConn;

	#Stores error messages for connection errors
    var $connectError;

	#MySQL constructor
    function MySQL ($host,$dbUser,$dbPass,$dbName) {
        $this->host=$host;
        $this->dbUser=$dbUser;
        $this->dbPass=$dbPass;
        $this->dbName=$dbName;
        $this->connectToDb();
    }

	#Establishes connection to MySQL and selects a database
    function connectToDb () {
        // Make connection to MySQL server
        if (!$this->dbConn = @mysql_connect($this->host,
                                      $this->dbUser,
                                      $this->dbPass)) {
            trigger_error('Could not connect to server');
            $this->connectError=true;
        // Select database
        } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
            trigger_error('Could not select database');
            $this->connectError=true;
        }
    }

	#Checks for MySQL errors
    function isError () {
        if ( $this->connectError )
            return true;
        $error=mysql_error ($this->dbConn);
        if ( empty ($error) )
            return false;
        else
            return true;
    }

	#Returns an instance of MySQLResult to fetch rows with
	function query($sql) {
		global $DB_NUM_QUERIES;
		$DB_NUM_QUERIES++;
        if (!$queryResource=mysql_query($sql,$this->dbConn))
            trigger_error ('Query failed: '.mysql_error($this->dbConn).
                           ' SQL: '.$sql);
        return new MySQLResult($this,$queryResource);
    }
}

#MySQLResult Data Fetching Class
class MySQLResult {

	#Instance of MySQL providing database connection
    var $mysql;

	#Query resource
    var $query;

	#MySQLResult constructor
    function MySQLResult(& $mysql,$query) {
        $this->mysql=& $mysql;
        $this->query=$query;
    }

	#Fetches a row from the result
    function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

	#Fetches a row from the result
    function fetch_row () {
        if ( $row=mysql_fetch_row($this->query) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

	#Returns the number of rows selected
    function size () {
        return mysql_num_rows($this->query);
    }

	#Returns the number of fields in table being queried
    function numFields () {
        return mysql_num_fields($this->query);
    }

	#Returns the field name from the query in the position you specify
    function FieldName ($field_number) {
        return mysql_field_name($this->query, $field_number);
    }

	#Returns the ID of the last row inserted
    function insertID () {
        return mysql_insert_id($this->mysql->dbConn);
    }
   
	#Returns the number of affected rows
    function affectedRows () {
        return mysql_affected_rows($this->mysql->dbConn);
    }

	#Checks for MySQL errors
    function isError () {
        return $this->mysql->isError();
    }
}
?>