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, May 31 '12 at 16:07

score 2 · Answer 1 · answered May 31 '12 at 16:12

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-31T16:11:05.503

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 '12 at 16:11

answered May 31 '12 at 16:05

Dan Blows

20,846
10
65
96

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. – Will May 31 '12 at 16:16
What OS and web server are you using? – Dan Blows May 31 '12 at 16:18
OS for the server? It's a generic LAMP. PHP version 5.2.17 – Will May 31 '12 at 16:19
@MobyD Do you have sudo access onto the server? – Dan Blows May 31 '12 at 16:20
Yes. I'm assuming I need to tack on PDO_DBLIB. – Will May 31 '12 at 16:23
@MobyD Actually, I think you just need to do `sudo apt-get install php5-mssql`. I haven't used MSSQL on a LAMP server before, so let me know if that returns problems. – Dan Blows May 31 '12 at 16:25
Higher ups aren't really thrilled about this idea so I'll stick to what I'm doing for now. Thank you anyways though. You gave me the correct answer, I just can't implement it at the moment. – Will May 31 '12 at 16:32
Just found this highly relevant question too http://stackoverflow.com/questions/5953882/connecting-to-mssql-using-pdo-through-php-and-linux – Dan Blows May 31 '12 at 16:57

OOPHP: Classes for SQL Server and MySQL

2 Answers2