DB access in every class (could this be done in a better way?)

poisa

So I have a table and a Class that represents it. Let's say a User class. Then I have a UserDB which selects, inserts, updates, and deletes data un the User table. So far so good.

Now lets say I have about 80 tables in a project and find it very suspicious that I have to write the same code (the one that selects, inserts, updates, and deletes) on every class. Could this be done in a better way? Could I write a generic select, update, insert, and delete class?

I'm pretty sure there is a better way to handle large scale projects (one of which I'm about to tackle).

Any help is greatly appreciated.

slick101

I guess you would need to create a base class and then inherit from that class.

class bcase_database{

}

class users extens bcase_database{
}

class inventory extends bcase_database{
}

and i guess you will need to do some inheritance stuff?

Weedpacket

One solution would be to create a new class/object to handle the database connection (look up the Singleton pattern, since generally you'd only want one object per connection) and the common stuff for working with it. Provide all of the objects that need to use this database object with methods that allow you to pass it in for them to use.

Bjom

Whether OOP with classes or other: SQL is simply a string. You can make classes functions whatever to create and manipulate such strings in any way. For example:

function buildSQL($tablename, $fieldarray){
    $sql = 'SELECT ';
    $sql .= $fieldarray[0];
    for($i = 1; $ub = count($fieldarray); $i < $ub; ++$i){
        $sql .= ', ' . $fieldarray[$i];
    }
    $sql .= ' FROM ' . $tablename . ';';
}

regards,

Bjom

poisa

Guys, thanks for the prompt response. I don't think I've made myself entirely clear even though all your responses are aimed in the right direction. I think the closest one to where I think I'm headed is slick101's response.

I guess it will be easier to show an example of what I mean in code rather than words.

I'm including a class used to interact with the Menu table. This is an extremely simplified version of a table that has two fields: id, and name. The thing I love about this class is that I made it so that as long as your table's primary key is called "id" and you fill the $_fields array with all your table fields, it will provide you with decent database functionality.

As you can see, the db access code is hard coded into the class and when you have many tables, there is a lot of repeated code. I'm happy with the code I wrote. I'm looking for a way to extract this code from each class and put it somewhere else so that when I have to maintain it, I can do it in one place and not in EVERY class that accesses a table. Not sure what "the correct" way of doing this is. OOP is sort of a new field for me.

Here it is:

<?php

class Menu {

// DB connection
protected $_db;

// Table name
protected $_tableName = 'menu';

// Table fields
protected $_id;
protected $_name;

protected $_fields = array (
'id',
'name'
);

public function __construct() {
    $this->_db = DB::getInstance();
}

public function getById($id) {
    $s_id = doubleval($id);

    $sql = "SELECT * FROM `" . $this->_tableName . "` WHERE `id` = $s_id LIMIT 1";

    $result = $this->_db->query($sql);

    if (mysql_num_rows($result) == 0) {
        return true;
    }

    $row = mysql_fetch_array($result, MYSQL_ASSOC);

	// Populate this class' properties with query results
    foreach ($row as $k=>$v) {
        $method = 'set_'.$k;
        $this->$method($v);
    }

    if (mysql_error() == '') {
        return true;
    } else {
        return false;
    }
}

public function insert() {

    $sql = "INSERT INTO `" . $this->_tableName . "` (";

    // Remember to filter out the 'id' field while inserting rows
    // as we want the DB to generate new ids

    foreach ($this->_fields as $k) {
        if ($k != 'id') {
            $sql .= "`$k`, ";
        }
    }

    // strip last comma from query
    $sql = substr($sql, 0, -2);

    $sql .= ') VALUES (';

    foreach ($this->_fields as $k) {
        if ($k != 'id') {
            $method = 'get_'.$k;
            $v = mysql_real_escape_string($this->$method());
            $sql .= "'$v', ";
        }
    }

    // strip last comma from query
    $sql = substr($sql, 0, -2);

    $sql .= ')';

    $result = mysql_query($sql);

    if (mysql_error() != '') {
        return false;
    } else {
        $this->getById(mysql_insert_id());
        return true;
    }
}


public function update() {
    $sql = "UPDATE `" . $this->_tableName . "` SET ";

    foreach ($this->_fields as $k) {
        if ($k != 'id') {
            $method = 'get_'.$k;
            $v = mysql_real_escape_string($this->$method());
            $sql .= "`$k` = '$v', ";
        }
    }

    // strip last comma from query
    $sql = substr($sql, 0, -2);

    $sql .= " WHERE `id` = ".doubleval($this->get_id());
    $result = mysql_query($sql);

    if (mysql_error() != '') {
        return false;
    } else {
        return true;
    }
}


public function delete() {
    $s_id = doubleval($this->get_id());

    $sql = "DELETE FROM `" . $this->_tableName . "` WHERE `id` = '$s_id' LIMIT 1";
    $result = mysql_query($sql);

    if (mysql_error() != '') {
        die (mysql_error());
        return false;
    } else {
        $this->reset();
        return true;
    }
}

protected function reset() {
    foreach ($this->_fields as $k) {
        $method = 'set_'.$k;
        $this->$method('');
    }
}

/**
 * Returns $_id.
 * @see Menu::$_id
 */
public function get_id() {
    return $this->_id;
}

/**
 * Returns $_name.
 * @see Menu::$_name
 */
public function get_name() {
    return $this->_name;
}

/**
 * Sets $_id.
 * @param object $_id
 * @see Menu::$_id
 */
public function set_id($_id) {
    $this->_id = $_id;
}

/**
 * Sets $_name.
 * @param object $_name
 * @see Menu::$_name
 */
public function set_name($_name) {
    $this->_name = $_name;
}

}
?>

dagon

autoload may work for you.

Roger_Ramjet

You are not the first person to address this problem. One solution of note is the PHP Object Generator project. By simply describing the 'object', POG will generate both the class definition and the create table sql for you. Simply extend the CRUD classes with any additional methods required. POG will even cope with objects that 'own' collections of other objects, or are owned by other objects.

If nothing else, you will pick up some very usefull pointers for your own solution.

Now, it is also possible to make a generic class that, when supplied with a table name, can fill itself with the field list, data types and even some basic validation (NULL, NOT NULL) by using SHOW or DESCRIBE. No need to worry about tha name of the autoinc as you can get that from the details, likewise primary key etc.

Now, rather than building sql strings for the queries, you can use PDO and prepared statements to bind your data to columns using the field list and thus make your insert/update/delete queries more generic.