I am developing an item-oriented PHP website at this time and am attempting to determine the easiest method to abstract database functionality in the relaxation from the system. At this time, I have got a DB class that handles all of the connections and queries the system uses (it's virtually an interface to MDB2). However, when utilizing this technique, I have recognized that I have got lots of SQL query strings turning up everywhere during my code. For example, during my User class, I have got something similar to 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 must do is discover the very best way of reducing the quantity of inline SQL. I realize that certain method of doing this is to create functions within User for each one of the queries that User utilizes (something similar to the next), but that can lead to a number of 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's the best way of controlling the great deal of queries that the typical database application would use? Would the way in which I referred to be the right way of handling this case? Or how about signing up a listing of queries inside the DB class and associating with every a distinctive ID (for example USER_CHECKLOGIN) that's passed in to the DB's query function? This process may also assist with security, because it would limit the queries that may be go to only individuals which are registered within this list, but it is yet another factor to consider when writing all of the class functions. Ideas?

Getting the SQL drawn out into separate functions is really a decent start. Another steps you can take:

  • Create separate classes for database access code. This helps make certain you do not have SQL functions scattered around in most of the PHP files.
  • Load the SQL from exterior files. This completely separates your SQL code as well as your PHP code, making both more maintainable.
  • Use saved methods when you are able. This removes the SQL out of your PHP code altogether, helping enhance your database security by reduction of the danger that exterior SQL can get performed.

You might like to consider applying the ActiveRecord Pattern. Utilizing a design pattern similar to this provides some consistency in how you train with data out of your tables. There might be some disadvantages to these kinds of approaches, mainly performance for several kinds of queries but it may be labored around.

An alternative choice could possibly be the utilization of an ORM, for PHP probably the most effective are:

Both permit you to access your database using some objects, supplying an easy API for storing and querying data, have their very own query language, that's converted internally towards the specific DBMS native SQL, this can ease moving programs in one RDBMS to a different with simple configuration changes. I additionally like because you can encapsulate datamodel logic to include validation for instance, only by stretching your model classes.