Guidance on PHP OOP DB Class

[whizard]

Hey all... I've become fairly experienced with PHP over the last few years, but I have been a strictly procedural coder. I am trying to branch out into OOP, but I need a little help. I am trying to build a class to connect with the DB and run queries and error check and stuff, but I'm having trouble visualizing exactly what functions I should have and if I need a class for connecting to the DB and a class for querying or if one class can handle both tasks.

So, if you gurus could shed a little light on this, I would appreciate it. (I don't need or really want actual PHP code, just words of advice and psuedocode telling me which functions I need and how to set up the class, etc)

Dan

[Fou-Lu]

One can handle both cases.
This really depends on how complex you want to get, it becomes easier as you do it more often.
What I like is a standard functionality that should exist in database functionality. For example, you'll want an open, close, a query, perhaps broken down select/update/insert/delete functions, etc.
The idea is you want to encapsulate everything you plan to use within a single object, but keep it flexible enough for easy database swapping. Keep your class as generic as you can.

Hope that helps you get an idea of what you need to do!

[whizard]

Thanks. So, for instance, would you advise creating seperate functions to connect to MySQL and to select a DB once connected? If there were seperate methods, how would the DB select method get the open MySQL link from the first method?

What about error handling? is that a seperate method?

Dan

[Fou-Lu]

You can separate whatever makes the most sense to you.
I do not separate the connection to the database with a separate database selection method. This is because I also use a DOM storage method to simulate database functionality in flat files. No database selection there.
Anything within an object can be accessed from that object. This is what the properties are for. A quick example:

class MySQLDriver
{
private $obHandle;

public function __construct($...)
{
$this->obHandle = mysql_connect($...);
if (!$this->obHandle)
{
$this->halt(mysql_errno(), mysql_error());
}
}
public function selectDB($dbName)
{
if (is_resource($obHandle))
{
// We'll use mysql for simplicity
$db = mysql_select_db($dbName, $this->obHandle);
if (!$db)
{
$this->halt(mysql_errno(), mysql_error());
}
}
}
public function halt($errno, $error)
{
die(sprintf('Fatal error (%d): %s', $errno, $error));
}
}

Simple example with a database selection. The idea is, if you change your database type to say oracle, you can program in the correct functionality without needing to change the code accessing the values.
The bigger pain in PHP is its weak datatyping. There is no way in an interface to specify what a return type is or what the values of parameters are (except DataHinted class object or arrays).

The more you use it the more familiar you will become with it. I factory my connections, so I only need to change the configuration values within a global config file to use whatever database I want. Of course, I need the driver files for them, but I've got the popular 4 done :)

[whizard]

hey thanks so much! It's nice to have a little bit of a roadmap when you're venturing out where you've never been before... :)

Dan

[whizard]

Another question... I know it is sort of bad practice to supress errors using '@', but if I am copying down the error info, does it make sense to use '@' on the mysql_ commands, so that I don't show two errors?

Dan

[Fou-Lu]

I don't think its bad practice to suppress errors. I think its bad practice to not handle errors on results. Obviously you will want to do something if you're unable to connect to the database, so if you check something like:

if (($link = @mysql_connect($...)) === false)
{
die (sprintf('unable to connect, %d: %s', mysql_errno(), mysql_error()));
}

You stop the message to the screen/log, but you still handle the invalid resource.
Totally up to you as to how you like it to be done. All you gotta remember is to do something with failures.

[whizard]

ok. Thanks! :)

[whizard]

Ok, so I've been working at this. For now, I decided to use only a general query method and not one for insert, one for delete, etc.

<?php

//Class to create and use a connection to a (MySQL) database

class databaseConnection
{
private $obHandle; //This will hold the mysql_connect link
private $errorList = array(); //This will store the MySQL error info.
public $result; //This will hold the result of a query

public function __construct($server,$user,$pass)
{
$this->obHandle = @mysql_connect($server,$user,$pass);

if(!$this->obHandle)
{
$this->logError(mysql_errno(),mysql_error());
return false;
}
else
{
return true;
}
}

public function selectDB($dbName)
{
if(is_resource($this->obHandle))
{
$db = @mysql_select_db($dbName,$this->obHandle);

if(!$db)
{
$this->logError(mysql_errno(),mysql_error());
return false;
}
else
{
return true;
}
}
return false;
}

public function query($query)
{
$this->result = @mysql_query($query,$this->obHandle);

if(is_resource($this->result))
{
return true;
}
else
{
$this->logError(mysql_errno(),mysql_error());
return false;
}
}

private function logError($errorNumber,$error)
{
$this->errorList[] = array("num" => $errorNumber, "desc" => $error);
}

public function printErrors()
{

foreach($this->errorList as $error)
{
print "MySQL Error #" . $error[num] . ": " . $error[desc];
}
}
}

If you look at the query method, you'll see that I put the results into a variable (property?). Should the query function return the results? if so, can I still return 'false' instead if the query fails? how can i handle querys within querys in loops?

[aedrin]

For now, I decided to use only a general query method and not one for insert, one for delete, etc.

Should the query function return the results? if so, can I still return 'false' instead if the query fails?

I like to look at other implementations (in other languages too) to see how they did it and perhaps use their elements.

It's generally a good idea to split up your query code into 2. One for retrieving data (select) and one for making modifications (insert, update, delete). This way your retrieval can return the data. And your modifier can return the number of rows affected.

For the second (modifier) success is simply checking how many rows were modified. If it is 0 you can generally assume (given the correct query) that it failed.

For the first, I would return null.

public $result; //This will hold the result of a query

I would advise against this. This will mean you are coding against a specific interface/implementation of the driver (with mysql, you'd now have to use mysql_num_rows(), etc.)

Create a very basic abstract QueryResult/ResultSet class/interface. Then for each database driver (mysql, oracle, pgsql, etc.) create a subclass. This way you can define how you want to deal with data.

I created a class where I can just call fetchRow() and it will return the next row. I also implemented the Iterator interface (which is part of PHP) so that I can do this:


$result = $db->query("SELECT * FROM table");

foreach ($result as $row) {
// $row contains the current row from the database
}



interface QueryResult {
public function fetchRow();
public function getRowCount();
}


That is really all that it needs. It gets the data through the constructor so in my mysql implementation I just do this:


return new MySQL_QueryResult($result);

[Fou-Lu]

Wow, lol, I do the same but I call mine fetchArray. Lol.
I'm with aedrin there, I wouldn't allow public access to a property, unless that property is final. Its a great start nonetheless :)

Hold on, I'll give you the interface I made in Java. Didn't want to allow use of resultSet objects so I converted them into hashmaps (thats what a PHP array is btw, PHP doesn't use real array's). Java handles resultsets a little differently, so I did all the fetching within the actual method. What a pain:

package interfaces;

import java.util.Map;

import Exceptions.MyDBException;

/**
* Contract a Database or storage object to implement these methods.
* Figured that ResultSet may not always be used, so we are forcing
* HashMaps
* @author Kevin Simpson
*/
public interface IMDB
{
/**
* Force this object to open
* @throws Exceptions.MyDBException
*/
public void open()
throws MyDBException;

/**
* Force this object to close
* @throws Exceptions.MyDBException
*/
public void close()
throws MyDBException;

/**
* Select from storage given the fields and from location
* @param fields
* @param from
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from)
throws MyDBException;

/**
* Select from storage given the fields, from and condition
* @param fields
* @param from
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition)
throws MyDBException;

/**
* Select from storage given the fields, from, condition, and order
* @param fields
* @param from
* @param condition
* @param order
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition,
String order)
throws MyDBException;

/**
* Select from storage given the fields, from, condition, order, and other
* @param fields
* @param from
* @param condition
* @param order
* @param other
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition,
String order, String other)
throws MyDBException;

/**
* Select from a RAW sql input
* @param sql
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> selectRaw(String sql)
throws MyDBException;

/**
* Get the last inserted ID
* @return
*/
public int lastInsertID();

/**
* Insert a record into storage given the items to insert and location
* @param fieldVals
* @param to
* @throws Exceptions.MyDBException
*/
public void insert(Map<String, Object> fieldVals, String to)
throws MyDBException;

/**
* Update in storage given the new values and where to write them to
* @param fieldVals
* @param to
* @return
* @throws Exceptions.MyDBException
*/
public int update(Map<String, Object> fieldVals, String to)
throws MyDBException;

/**
* Update in storage given the new values, where to write, and a condition
* @param fieldVals
* @param to
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public int update(Map<String, Object> fieldVals, String to,
String condition)
throws MyDBException;

/**
* Delete from a given storage location
* @param from
* @return
* @throws Exceptions.MyDBException
*/
public boolean delete(String from)
throws MyDBException;

/**
* Delete from a given storage location given a condition
* @param from
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public boolean delete(String from, String condition)
throws MyDBException;
}

[aedrin]

Hold on, I'll give you the interface I made in Java. Didn't want to allow use of resultSet objects so I converted them into hashmaps (thats what a PHP array is btw, PHP doesn't use real array's). Java handles resultsets a little differently, so I did all the fetching within the actual method. What a pain:


I didn't like using the ResultSet either, but felt that a HashMap was going a bit too far. So I ended up using a CachedRowSet (or any variation of RowSet works really) which is like a ResultSet but can be disconnected from the database (by loading data in memory with a single line). It can also be made to work with files (so you can next() through each line, etc.)

[Fou-Lu]

The original implementation was with files, thats why I choose the hashmap. I would have used the cachedRowSet, but I found it unpleasant for working with files and xml - I may have been using it incorrectly, but couldn't be arsed to spend the time looking ;). On the otherhand, my first language was PHP, so I was quite used to hashmaps and felt it was the easiest conversion between resultsets and an iterable object.
My newest implementation for java is similar but uses a custom return object that is similar to a hashmap. All it really did was change the ugly Map<Integer, Map<String, String>> to MyResult. Lol.

[whizard]

Wow, this is great! I'm just soaking all of this in!

So basically, what you guys are saying for returning results is (with a PHP/MySQL application) that I should create a new abstract data type which handles the results of a query. And the constructor of that class/interface/whatever should transform the result object into a PHP array (hashmap)?

Sorry for being so dense here. I really appreciate everything you guys are saying... it's really helpful! I feel like I'm learning more right here than I learned in an entire two semesters of CSC 101/102 intro to programming classes haha

Dan

[Fou-Lu]

I wouldn't do it that way with PHP. Since your database resource will return a record resource, you can throw it into your own custom fetchArray/fetchRow function. Part of the benefit of being born from a procedural language. That way you can do like you've always done:

$res = $myDBObj->select('SELECT * FROM `table`');
while ($rec = $myDBObj->fetchArray($res))
{
}

In Java theres a difference as to how it handles the records if I can recall correctly. I can't recall exactly what it is, but it will lose track of any recordset that it has been given, or at least a pointer to it so you can't iterate it correctly. Thats why I always chose to return a record set of somekind, your options are to create your own, or as aedrin mentioned java has a cachedRowSet object to take care of that with.

[whizard]

oh wow. that's gonna be helpful!

Thanks again
Dan