Page 1 of 1

[PROJECT] MySQL Database Class

Posted: Thu Jul 13, 2006 10:51 pm
by Benjamin
I might be reinventing the wheel here but is anyone interested in creating a nice database class we can all use? I started on one and what I have below is posted below. If you guys are interested maybe we can perfect it and move it over to snippets or something.

I haven't tested it 100% but I believe most if not all of it should work flawlessly.
It also supports joins.

EDITS:
1. Modifed __construct to connect when the class is instantiated.
2. Added resetMe function to prevent multiple queries from interfering with each other.

Code: Select all

class databaseConnection {
    function __construct($host, $username, $password) {
        $this->database = '';
        $this->query = '';
        $this->fields = '';
        $this->table = '';
        $this->parameters = '';
        $this->limit = '';
        $this->orderby = '';
        $this->joins = '';
        $this->linkid = '';
        $this->connected = false;
        $this->dbselected = false;

        if (!$this->linkid = @mysql_connect($host, $username, $password)) {
            $this->displayError(mysql_errno(), mysql_error());
        }
        $this->connected = true;
    }

    function selectDatabase($database) {
        if (!@mysql_select_db($database, $this->linkid)) {
            $this->displayError(mysql_errno(), mysql_error());
        }
        $this->dbselected = true;
    }

    function dispatch($action, $data) {
        if (!$this->connected) {
            $this->displayError('NA', 'Not connected to database.');
        }
        if (!$this->dbselected) {
            $this->displayError('NA', 'No database selected.');
        }
        if ($this->table == '') {
            $this->displayError('NA', 'No table selected.');
        }
        switch ($action) {
            case 'select':
                return $this->selectData($data);
                break;
            case 'insert':
                return $this->insertData($data);
                break;
            case 'update':
                return $this->updateData($data);
                break;
            case 'delete':
                return $this->deleteData();
                break;
            default:
                $this->displayError('NA', 'No valid action selected.  Options are select, insert, update and delete');
        }
    }

    function selectFields($fields) {
        $fieldlist = null;
        $fields = explode(" ", $fields);
        $fieldCount = count($fields) - 1;
        for ($i = 0; $i <= $fieldCount; $i++) {
            if (substr_count($fields[$i], '.') == 0) {
                $fieldlist .= "`" . mysql_real_escape_string($fields[$i]) . "`, ";
            } else {
                list($table, $fieldname) = explode('.', $fields[$i]);
                $fieldlist .= "`" . mysql_real_escape_string($table) . "`.`" . mysql_real_escape_string($fieldname) . "`, ";
            }
        }
        $fieldlist = substr($fieldlist, 0, -2) . " FROM ";
        return $fieldlist;
    }

    function addJoin($newtable, $newtablejoinfield, $primaryjoinfield, $jointype = null) {
        $this->joins .= $jointype . " JOIN `" . mysql_real_escape_string($newtable) . "` ON (`";
        $this->joins .= $this->table . "`.`" . mysql_real_escape_string($primaryjoinfield) . "` = `";
        $this->joins .= $newtable . "`.`" . mysql_real_escape_string($newtablejoinfield) . "`) ";
    }

    function selectData($fields) {
        $this->query = "SELECT " . $this->selectFields($fields) . "`" . $this->table . "` ";
        if ($this->joins != '') {
            $this->query .= $this->joins;
        }
        if ($this->parameters != '') {
            $this->query .= "WHERE ";
            while (list($Field, $Value) = each($this->parameters)) {
                $Value = explode(" ", $Value);
                $this->query .= " `" . mysql_real_escape_string($Field) . "` " . mysql_real_escape_string($Value[0]) . " '" . mysql_real_escape_string($Value[1]) . "' AND ";
            }
            $this->query = substr($this->query, 0, -4);
        }
        if ($this->orderby != '') {
            $this->orderby = mysql_real_escape_string($this->orderby);
            $this->query .= "ORDER BY " . $this->orderby . ' ';
        }
        if ($this->limit != '') {
            $this->limit = mysql_real_escape_string($this->limit);
            $this->query .= "LIMIT " . $this->limit . " ";
        }
        return $this->sendQuery();
    }

    function resetMe() {
        $this->fields = '';
        $this->table = '';
        $this->parameters = '';
        $this->limit = '';
        $this->orderby = '';
        $this->joins = '';
    }

    function sendQuery() {
        if (!$this->linkid = @mysql_query($this->query)) {
            $this->displayError(mysql_errno(), mysql_error());
        }
        $this->resetMe();
        return $this->linkid;
    }

    function insertData($data) {
        $this->query = "INSERT INTO `" . $this->table . "` (";
        while (list($fields, ) = each($data)) {
          $this->query .= "`" . mysql_real_escape_string($fields) . "`, ";
        }
        $this->query = substr($this->query, 0, -2) . ") values (";
        reset($data);
        while (list(, $values) = each($data)) {
          $this->query .= "'" . mysql_real_escape_string($values) . "', ";
        }
        $this->query = substr($this->query, 0, -2) . ")";
        return $this->sendQuery();
    }

    function updateData($data) {
        $this->query = "UPDATE `" . $this->table . "` SET ";
        while (list($fields, $values) = each($data)) {
          $this->query .= "`" . mysql_real_escape_string($fields) . "`" . "='" . mysql_real_escape_string($values) . "', ";
        }
        if ($this->parameters == '') {
            $this->displayError('NA', 'Cowardly refusing to perform table update without WHERE clause.');
        }
        $this->query = substr($this->query, 0, -2) . " WHERE ";
        while (list($field, $value) = each($this->parameters)) {
          $value = explode(" ", $value);
          $this->query .= "`" . mysql_real_escape_string($field) . "` " . mysql_real_escape_string($value[0]) . " '" . mysql_real_escape_string($value[1]) . "' AND ";
        }
        $this->query = substr($this->query, 0, -4);
        return $this->sendQuery();
    }

    function deleteData() {
        $this->query = "DELETE FROM `" . $this->table . "` WHERE ";
        if ($this->parameters == '') {
            $this->displayError('NA', 'Cowardly refusing to perform delete query without WHERE clause.');
        }
        while (list($field, $value) = each($this->parameters)) {
            $Value = explode(" ", $value);
            $this->query .= "`" . mysql_real_escape_string($field) . "` " . mysql_real_escape_string($value[0]) . " '" . mysql_real_escape_string($value[1]) . "' AND ";
        }
        $this->query = substr($this->query, 0, -4);
        return $this->sendQuery();
    }

    function fetchRow() {
        if ($this->row = mysql_fetch_assoc($this->linkid)) {
            return $this->row;
        } else {
            return false;
        }
    }

    function getInsertID() {
        // need to test for a valid linkid here or place an & in front of mysql_insert_id
        return mysql_insert_id($this->linkid);
    }

    function getNumRows() {
        // need to test for a valid linkid here or place an & in front of mysql_num_rows
        $this->numrows = mysql_num_rows($this->linkid);
        return $this->numrows;
    }

    function getAffectedRows() {
        // need to test for a valid linkid here or place an & in front of mysql_affected_rows
        return mysql_affected_rows($this->linkid);
    }

    function cleanup() {
        // need to test for a valid linkid here or place an & in front of mysql_free_result
        mysql_free_result($this->linkid);
    }

    function displayError($ErrorNumber, $ErrorText) {
        echo '<h2>Database Error</h2>' . "\n"
            .'<p>There was an error processing a database command.</p>' . "\n"
            .'<p>' . $this->query . "</p>\n"
            .'<p>Error #' . $ErrorNumber . "</p>\n"
            .'<p>Error Description: ' . $ErrorText . "</p>\n";
    }
}

Here are some usage examples...

Code: Select all

// connect and select database
$Database = new databaseConnection($host, $username, $password);
$Database->selectDatabase($database);

// select table
$Database->table = 'members';

// retrieve records
// set where clause, supports =, !=, <> and all that
$Database->parameters = array('username' => '= ' . $_POST['username']);
$Database->limit = '1';
$Database->dispatch('select', 'user_id user_status user_password user_login_tries');


// insert a record
$Database->table = 'members';
$newUser = array('user_status' => '0',
                              'username'    => $this->username,
                              'user_password' => md5($this->password),
                              'user_lastvisit' => time(),
                              'user_regdate'   => time(),
                              'user_level'     => 0,
                              'user_email'     => $this->email,
                              'user_actkey'    => '0000');
$Database->dispatch('insert', $newUser);

Posted: Thu Jul 13, 2006 10:59 pm
by RobertGonzalez
How come you don't establish the connetion in the constructor? It seems to make sense to me that initializing the object should at the very least establish a connection. Just an opinion.

Posted: Thu Jul 13, 2006 11:02 pm
by Benjamin
I don't know.. Should it be done that way? This is only the 2nd or 3rd class that I have written so I'm new at this.

Posted: Thu Jul 13, 2006 11:07 pm
by RobertGonzalez
It is just an opinion, but if I am going to initialize a DB interface object, I want at the very least to establish the connection. At that rate, if the connection fails, you can back out of the class.

I have seen several DB classes. Just about all of them do this. I am not saying it is a good way to do things because of that, but it does make sense to me to do that.

Code: Select all

<?php
$db = new SQL_Ops($host, $username, $password, $database);
if (!$db)
{
    die('Man, we are sooo screwed...');
}
?>
So now the $db object is either connected to the server and using the database, or you are dead. What do you think?

Posted: Thu Jul 13, 2006 11:21 pm
by Benjamin
I can't think of any reason why it wouldn't be better to do it the way you recommended.

Posted: Fri Jul 14, 2006 12:15 am
by Christopher
I'm interested and think it might be a useful thing to have around. But unfortunately your design is a bit offbeat because it is trying to do at least two things. I think the dispatch method in itself would make many programmers look elsewhere.

I would suggest a different approach. The basis of your code above is a Connection class. Those things are pretty standard and with enough cooks we could come up with a minimal one that people might even use -- if it was very mininal.

So the first part of the approach I would suggest is to make the thing really minimal. The reason is because there are so many kitchen-sink database classes out there that another super-deluxe one is just not necessary. And I would probably follow the lead of PDO and the Zend Framework in its general design -- but remove the bells and whistles.

The second part of the approach I would suggest has to do with the nature of a Connection class. The thing about a Connection class is that it is not really for public consumption -- it is meant to be passed to Gateways and Mappers and ActiveRecord classes as a polymorphic service/data layer object. So the real question about the design of a Connection class is: What do those other classes need from it? It should provide no more than the minimal interface needed by the classes that use it.

So what you really need to start with is minimal implementations for say ActiveRecord, TableDataGateway, and O/R Mapper. And that's easier said than done. However, if you admit from the start that they will be fairly limited but generally useful, and require that the mapping be done manually by the programmer (in code), then it is not that difficult a task. And as long as the whole things stays minimal, it is potentially a pretty handy set of classes.

Posted: Fri Jul 14, 2006 12:57 am
by Benjamin
arborint wrote:I'm interested and think it might be a useful thing to have around.
Ok Great!
arborint wrote:I think the dispatch method in itself would make many programmers look elsewhere.
I added the dispatcher so I wouldn't have to remember the names of all the different functions. These functions can still be called directly though. In fact you can even call the query function directly to run a custom query, bypassing many other functions. Does that make a difference?

arborint wrote:So the first part of the approach I would suggest is to make the thing really minimal. The reason is because there are so many kitchen-sink database classes out there that another super-deluxe one is just not necessary. And I would probably follow the lead of PDO and the Zend Framework in its general design -- but remove the bells and whistles.
Are you saying split it in half and create a seperate connection class? I figured queries and connections go together, and I was following the "keep dependencies to a minimum" approach.

As for the rest of the things you said, they are over my head at the moment. What is the first step to take?

Posted: Fri Jul 14, 2006 2:22 am
by Christopher
astions wrote:Are you saying split it in half and create a seperate connection class? I figured queries and connections go together, and I was following the "keep dependencies to a minimum" approach.
I think "keep dependencies to a minimum" is not exactly the goal, even if it is a good things to do. I say keep dependencies clear and in the right directions.

I would split it into two classes -- mainly because that is what is usually see done by good programmers -- and I assume they have good reasons for doing it. What you have shown above is a class that combines a DBConnection class with a TableDataGateway. My guess as to why they are usually separated is that those two functions are in different layers in an application. The DBConnection class is in the data layer -- it is abstracting some subsystem and its design really has nothing to do with your application. The TableDataGateway class is in the domain layer -- its design is defined by and defines your application. However there is the question of where to put a generic TableDataGateway like you have shown and whether building such a thing is even a good idea.

Posted: Fri Jul 14, 2006 4:27 am
by Benjamin
Well I don't have a solid understanding of the layering concepts. My main reason for creating this class was to save time. Almost every single web page I make has multiple database queries. Quite a few have long inserts, selects etc.

This class does save me time, and I figured it could help save others time as well. Please correct me if my thinking is flawed on that.

Perhaps the class should be divided. I don't see any reason not to do it, but I besides for the layers you mentioned, I don't see any reason to do it either.

Another advantage of using this class is that when it is used to generate queries, it automatically escapes the data as well. I'm just trying to shave off coding time whenever I can.

This might not be suitable for everyone, and it might not save everyone time. But I would hope that it benefits others.

Posted: Fri Jul 14, 2006 7:24 am
by Benjamin
Ok, they do need to be seperated in order to allow for multiple queries using the same linkid. Otherwise it will call mysql_connect for each instance. I'll edit the code in a bit.

Posted: Fri Jul 14, 2006 8:41 am
by Jenk
You don't need to instantiate a new object for every query.

use the resetMe() method and rebuild the query string, then execute it.

Posted: Fri Jul 14, 2006 10:03 am
by Benjamin
I'm talking about a nested query like this...

while get categories {
while get products {
}
}