MySQL Class Theory

[steelmanronald06]

Right, so I want to create a mysql class to make it easier on me personally to execute scripts, and to control my errors.  So, there are two ways that I "THINK" I can approach this.  What I need is for someone to first tell me if either or both will work, and then what they would suggest....also, other suggestions apart from the ones I put are most welcome.

 

So, way number one:

 

I have more defined functions like:

 

function SelectClass($fields, $where=null)

{

if (!$where)

{

mysql_query("SELECT * FROM $fields");

}else{

mysql_query("SELECT * FROM $fields WHERE $where");

}

}

 

$fields would be the field names listed like so ($fields = "'field1', 'field2', 'field3') and $where would be like ($where = "`field1`='blah' AND `field2`='blib'"

 

Or, i could go like this:

 

function query($sql)

{

mysql_query($sql);

}

 

Okay, for the second one you ask why do I even need a function for it!  But, I plan on putting in some error handling and stuff to, to make it really clean looking should anything go wrong with my queries.

[Buyocat]

What you're looking for is actually a very complicated problem. The second approach is actually the easier one, and there many packages available which do that already.  For example, take a look at PDO (an extension for PHP 5), Adodb, and MDB2 (PEAR solution).

 

As for the second option that road leads to an object relation mapper.  The real challenges there are capturing more complicated queries, for instance join queries.  For simpler quries that only take place over a single table then it is much more straight forward, and I personally like the option because your queries are mostly written for you once the program is written.

 

If you'd like to talk more about implementation details let me know and we can chat some more.  I'm actually working on an ORM now.

[steelmanronald06]

I've used libraries such as PEAR and AdoDB, but I want something more "custom".  As for the more complicated queries, such as join queries, I don't ever use join quries and if i did, i could do it procedural way.  The mysql class is basically for those mysql functions I use most often. It will allow me to be able to call them easier and i can control the error outputs and stuff.  But I want it to be very lightweight.  Pre-made pacakges, like AdoDB and PEAR, are to heavy and I use about 1/100 of the stuff in them.

[Buyocat]

I certainly understand what you're saying.  First let me suggest you take a look at something I've written, it's available in my signature.  It's a class which wraps around MySQL queries like the second solution you thought of.  As for the first one I think that would be very useful, but should probably be built on top of the second idea acting as a foundation layer.  That way you could switch out MySQL to say Sqlite in the future without having to rewrite much code.

[keeB]

Basically... here's what I do.

 

<?php
abstract class Database {
   #Database class template
   abstract function query($q);
   abstract function connect();

}

class MySQL extends Database {
    private $connection;

    function MySQL($host, $username, $password, $database) {
       $this->host = $host
        .
        .
      $this->database = $database
    }

    function connect() {
      $this->connection = mysql_connect($this->host, $this->username ... );
    }
  
}

class DBUpdater extends MySQL {

    function DBUpdater() {
      MySQL::MySQL("host", "user", "password", "db");
    }

    public function createUser($userName) {
      $q = "INSERT INTO USERS (userName) VALUES ('$userName')";
      result = $this->query($q);
    }

}
?>

 

Didn't check for errors, as I typed it in here, but you should understand the concept..

[keeB]

A little more explanation, the reason I abstract the functionality so much is because I have multiple bases covered here.

 

1. Standard Database object template, so I know any objects I derive from Database can be swapped by my lower level code. If I ever decide mysql isn't good enough, I should be able to swap it out and not have to change a thing in my DBUpdater class.

 

2. The MySQL object is reusable in every project I need DB access in, creating less effort the next time I need to.

 

3. Common functions are encapsulated and isolated in one place. This makes debugging a lot easier.

[roopurt18]

If you anticipate working with databases other than MySQL, I would consider a purely abstract base DB class that exposes the most common DB interactions: select, insert, update, delete, etc.  If you only need functionality with MySQL databases, then you can skip the abstract class and just create a class exposing those functions:

 

I personally just pass a complete SQL statement into any DB class that I create and use an additional layer to build the SQL statements.

 

For example:

<?php
class TopicDAO{
  // This DAO object would have a member pointing at an instance of the DB class

  /**
   * getTopics
   * Returns an array of topic information.
   * string $category Topic category
   */
  function getTopics($category = null){
    $Clean = Array();
    $Clean["Cat"] = $this->m_db->sanitize($category);
    // If category is set, we will have a WHERE clause
    $Wheres = Array();
    if(isset($category)){
      $Wheres[] = "cat={$Clean['Cat']}";
    }
    // Build the SQL
    $sql = "SELECT * FROM topics";
    if(count($Wheres)){
      $sql .= " " . implode(" AND ", $Wheres);
    }
    $sql .= " ORDER BY name";
    return $this->m_db->select($sql);
  }
}
?>

 

Using a system like this it is very easy to convert optional or extra function parameters into part of the WHERE clause.  An optional $order parameter could also be specified.  The database error checking should occur in the DB class and not the DAO class.  This way the application can just request data from DAO objects and not worry about the underlying database structure.

 

If the underlying DB structure or engine is changed, the application code doesn't need to be modified.  Just the code within the DAO layer of the affected areas.

 

In my current project, the DB class is really just a wrapper for mysql_query() calls.  Each of the functions select(), insert(), update(), etc. just accepts a full SQL statement and performs the proper checking on the value returned by mysql_query() to determine success.

 

Since the application layer shouldn't have any real knowledge about how the underlying database, all of the DAO retrieval operations return arrays.  In order to accomplish this, the DB class's select() function has to loop over the returned resource at least once and typically the application will loop over it again.  This is fine for small return sets but horribly inefficient for large return sets.  On my next project I plan to develop the DB class to return an iterator object that allows iteration over the records as if they were an array but still provide a way for the application layer to not care about what database engine is under the hood.