Separating code from DB functionality

Question

I'm developing an object-oriented PHP website right now and am trying to determine the best way to abstract database functionality from the rest of the system. Right now, I've got a DB class that manages all the connections and queries that the system uses (it's pretty much an interface to MDB2). However, when using this system, I've realized that I've got a lot of SQL query strings showing up everywhere in my code. For instance, in my User class, I've got something like this:

function checkLogin($email,$password,$remember=false){
    $password = $this->__encrypt($password);
    $query = "SELECT uid FROM Users WHERE email=? AND pw=?";

    $result = $this->db->q($query,array($email,$password));

    if(sizeof($result) == 1){
       $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
       $uid = $row['uid'];
    }else{
       return false;
    }

    /* Rest of the login script */
}

What I would like to do is find out the best technique for reducing the amount of inline SQL. I understand that one way to do this would be to write functions within User for each of the queries that User makes use of (something like the following), but that could lead to quite a few functions.

function checkLogin($email,$password,$remember=false){
    $password = $this->__encrypt($password);
    $uid = $this->do_verify_login_query($email,$password);

    /* Rest of the login script */
}

function do_verify_login_query($email,$encpw){
    $query = "SELECT uid FROM Users WHERE email=? AND pw=?";
    $result = $this->$db->q($query,array($email,$encpw));

    if(sizeof($result) == 1){
       $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
       return $row['uid'];
    }else{
       return false;
    }
}

So...my question. What is the best technique for managing the large amount of queries that a typical database application would use? Would the way I described be the proper way of handling this situation? Or what about registering a list of queries within the DB class and associating with each a unique ID (such as USER_CHECKLOGIN) that is passed into the DB's query function? This method could also help with security, as it would limit the queries that could be run to only those that are registered in this list, but it's one more thing to remember when writing all the class functions. Thoughts?

Answer 1

Having the SQL pulled out into separate functions is a decent start. Some other things you can do:

• Create separate classes for database access code. This will help make sure you don't have SQL functions scattered around in all of your PHP files.
• Load the SQL from external files. This completely separates your SQL code and your PHP code, making both more maintainable.
• Use stored procedures when you can. This removes the SQL from your PHP code altogether, and helps improve your database security by reducing the risk that external SQL will get executed.

Answer 2

You might want to look into implementing the ActiveRecord Pattern. Using a design pattern such as this provides some consistency in how you work with data from your tables. There can be some downsides to these sorts of approaches, mainly performance for certain types of queries but it can be worked around.