[PROJECT] MySQL Database Class

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

[PROJECT] MySQL Database Class

Post 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);
Last edited by Benjamin on Fri Jul 14, 2006 2:14 am, edited 2 times in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I can't think of any reason why it wouldn't be better to do it the way you recommended.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I'm talking about a nested query like this...

while get categories {
while get products {
}
}
Post Reply