OOPHP: Classes for SQL Server and MySQL

Question

I'm working on a Web App that queries data from a clients SQL Server database. The credentials to access said database are stored in a MySQL database on our server. Querying them separately really isn't that hard, (Two letter difference with functions), but I recently had my "Oh god what a mess" moment and I'm trying to make things a little tidier.

In comes OOP. So now I've got basic CRUD classes in separate files for MySQL and SQL Server, but I feel like it's totally not necessary to have two different includes for such similar (Two letters, come on...) functionality.

I tried to add both classes to one file, didn't work.

So what's the best best practice here?

I had class MySQLDB and class MSSQLDB in file CRUD.php. Identical in every way except name and My functions were converted to Ms functions. I tried it and when I called a new MSSQLDB after calling a new MySQLDB, it just said it had no idea what a MSSQLDB was. — Will
– Will, Commented May 31, 2012 at 16:07

alganet · Accepted Answer · 2012-05-31 16:12:57Z

A good approach would be to use PDO, which abstracts the database connections for several engines (MySQL, MSSQL, PostgreSQL, etc). It's already OOP as well.

If your code is legacy and you can't change to PDO, there are a couple of alternatives. You can abstract much of the common code between the two CRUD classes using reflection:

abstract class AbstractLegacyConnection
{
    protected $connectFunction = '';
    protected $queryFunction = '';

    public function connect($host, $user, $pass) 
    {
        return call_user_func($this->connectFunction, $host, $user, $pass);
    }

    public function query($sql) 
    {
        return call_user_func($this->queryFunction, $sql);
    }
}

class MySQLLegacyConnection extends AbstractLegacyConnection
{
    protected $connectFunction = 'mysql_connect';
    protected $queryFunction = 'mysql_query';
}

Dan Blows · Accepted Answer · 2012-05-31 16:11:05Z

1

Use a PDO object, with the relevant driver. There are drivers for MySQL and for MSSQL. Use a factory generate the right PDO object, and then use that object to actually write to the database.

For example (pseudo-ish code):

$PDO = PDOFactory::getPDO( $database_type, $username, $password );
$PDO->write($data);

Your CRUD doesn't care about which PDO it's working with, only that it's something with a PDO interface. If you change database again, you just update the database type. In addition, for unit testing, you can pass in a mock PDO.

One thing to bear in mind - not all databases offer the same functionality. Something can work in MSSQL that doesn't work in MySQL. In this case, you can wrap the CRUD statement in an exception.

Also, consider looking at ORMs like Doctrine and Propel. They will teach you a lot, even if you choose not to implement them.

edited May 31, 2012 at 16:11

answered May 31, 2012 at 16:05

Dan Blows

21.2k10 gold badges68 silver badges99 bronze badges

8 Comments

Will Over a year ago

Since you are the only one who mentioned PDO drivers, how does one install the MSSQL one? PDO drivers sqlite, sqlite2, mysql is what my phpinfo() says.

Dan Blows Over a year ago

What OS and web server are you using?

Will Over a year ago

OS for the server? It's a generic LAMP. PHP version 5.2.17

Dan Blows Over a year ago

@MobyD Do you have sudo access onto the server?

Will Over a year ago

Yes. I'm assuming I need to tack on PDO_DBLIB.

|

Collectives™ on Stack Overflow

OOPHP: Classes for SQL Server and MySQL

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related