MySQL access classes in PHP

Question

I have a connection class for MySQL that looks like this:

class MySQLConnect
{
    private $connection;
    private static $instances = 0;

    function __construct()
    {
        if(MySQLConnect::$instances == 0)
        {
            //Connect to MySQL server
            $this->connection = mysql_connect(MySQLConfig::HOST, MySQLConfig::USER, MySQLConfig::PASS)
                or die("Error: Unable to connect to the MySQL Server.");
            MySQLConnect::$instances = 1;
        }
        else
        {
            $msg = "Close the existing instance of the MySQLConnector class.";
            die($msg);
        }
    }

    public function singleQuery($query, $databasename)
    {
        mysql_select_db(MySQLConfig::DB, $this->connection)
            or die("Error: Could not select database " . MySQLConfig::DB . " from the server.");
        $result = mysql_query($query) or die('Query failed.');
        return $result;
    }

    public function createResultSet($query, $databasename)
    {
        $rs = new MySQLResultSet($query, MySQLConfig::DB, $this->connection ) ;
        return $rs;
    }

    public function close()
    {
        MySQLConnect::$instances = 0;
        if(isset($this->connection) ) {
                mysql_close($this->connection) ;
                unset($this->connection) ;
        }
    }

    public function __destruct()
    {
        $this->close();
    }
}

The MySQLResultSet class looks like this:

class MySQLResultSet implements Iterator
{
    private $query;
    private $databasename;
    private $connection;
    private $result;

    private $currentRow;
    private $key = 0;
    private $valid;

    public function __construct($query, $databasename, $connection)
    {
        $this->query = $query;
        //Select the database
        $selectedDatabase = mysql_select_db($databasename, $connection)
            or die("Error: Could not select database " . $this->dbname . " from the server.");
        $this->result = mysql_query($this->query) or die('Query failed.');
        $this->rewind();
    }

    public function getResult()
    {
        return $this->result;
    }

//  public function getRow()
//  {
//      return mysql_fetch_row($this->result);
//  }

    public function getNumberRows()
    {
        return mysql_num_rows($this->result);
    }

    //current() returns the current row
    public function current()
    {
        return $this->currentRow;
    }

    //key() returns the current index
    public function key()
    {
        return $this->key;
    }

    //next() moves forward one index
    public function next()
    {
        if($this->currentRow = mysql_fetch_array($this->result) ) {
            $this->valid = true;
            $this->key++;
        }else{
            $this->valid = false;
        }
    }

    //rewind() moves to the starting index
    public function rewind()
    {
        $this->key = 0;
        if(mysql_num_rows($this->result) > 0) 
        {
            if(mysql_data_seek($this->result, 0) ) 
            {
                $this->valid = true;
                $this->key = 0;
                $this->currentRow = mysql_fetch_array($this->result);
            }
        }
        else
        {
            $this->valid = false;
        }
    }

    //valid returns 1 if the current position is a valid array index
    //and 0 if it is not valid
    public function valid()
    {
        return $this->valid;
    }
}

The following class is an example of how I am accessing the database:

class ImageCount
{
    public function getCount()
    {
        $mysqlConnector = new MySQLConnect();
        $query = "SELECT * FROM images;";
        $resultSet = $mysqlConnector->createResultSet($query, MySQLConfig::DB);
        $mysqlConnector->close();
        return $resultSet->getNumberRows();
    }
}

I use the ImageCount class like this:

if(!ImageCount::getCount())
{
    //Do something
}

Question: Is this an okay way to access the database? Could anybody recommend an alternative method if it is bad?

Thank-you.

Answer 1

Hey Mike, there's nothing wrong with implementing your own classes to handle database connection, what you have so far is fine, however PHP already provides an interface for handling DB connections regardless of the database manager you are connecting to. I'd recommend you to take a look at it http://www.php.net/manual/en/book.pdo.php since it has mostly all the functionality needed for handling queries, statements, resultsets, errors, and so forth.

Cheers, M.

Answer 2

I'm not sure that having a class called "ImageCount" is really necessary. If you're going to be working with images - I would simply have a class called "Image" with a static function to get the count of all images, and some other functions to deal with images.

Also, if you try to create a new instance when one exists - how about returning the existing instance instead of using die() to stop the program.