PHP Database class - select function

Question

I worked with procedural PHP for a long time but not to long ago I just started to learn OOP PHP. For better understanding I decided to create a class to manage my DB. As I started to learn from phpacademy my first select function was quite poor, so I just added some other arguments. I ended up with this:

public function get($tabel, $where = null, $columns = array('*'), $other = null){
    if($where){ $where = $this->where($where);; }
    $select = 'SELECT '.$this->select($columns);
    return $this->action($select, $tabel, $where, $other);
}
// $db->get('users',array('group',1),array(*),array('LIMIT' => 10));

(action executes the query) Then I decided to modify this to get better control.

public function getModified($table, $param = array()){
    $select = (isset($param['S'])) ? $this->select($param['S']) : '*';
    $where = (isset($param['W'])) ? $param['W'] : array();
    $other = array();
    if(isset($param['GB'])){ $other['GROUP BY'] = $param['GB']; }
    if(isset($param['OB'])){ $other['ORDER BY'] = $param['OB']; }
    if(isset($param['L'])){ $other['LIMIT'] = $param['L']; }

    return $this->action('SELECT '.$select, $table, $where, $other);
}
// $db->getModified('users',array('WHERE' => array('id',1), 'LIMIT' => 10));

But today I found in FuelPHP's documentation this: DB::get()->from('users')->where('id', 1)->limit(10); Because I do this class to practice OOP PHP I've tried to create something similar but to execute the query I had to add an other function, which I want to skip. Could you show me an example how this method should/could work?

And I know that it's objective but which one would you prefer?

Answer 1

I'll just explain how the FuelPHP way works. This is btw a pattern that is used in a lot of DB wrapper classes.

In short, your Database package consists of 2 classes. 1 that handles connection to your database, multiple connections,... This is the DB class. this will look something like this:

class DB
{
    private static $connections = array();

    public static function addConnection($name, $connection)
    {
        self::$connection[$name] = $connection;
    }

    public static function get($name='default')
    {
        return new QueryBuilder(self::$connection[$name]);
    }
}

This class manages all connections and returns a queryBuilder instance if you need to query a connection. The QueryBuilder will look something like this:

class QueryBuilder
{

    private $connection;

    public function __construct($connection)
    {
        $this->connection = $connection;
    }

    public function select()
    {
        $this->queryType = 'SELECT';
        return $this;
    }

    public function from($table)
    {
        $this->table = $table;
        return $this;
    }

    public function all()
    {
        return $this->connection->query(
            $this->getSqlQuery()
        );
    }

    public function getSqlQuery()
    {
        return $this->queryType . ' ' . $this->columns . ' FROM ' . $this->table;
    }
}

so now you can use the clases above as:

DB::setConnection('default', $myPdoConnection);

DB::get()->select()->from('my_table')->all();

Note that the Querybuilder assumes that your $connection has a query method

A nice example from the Eloquent/laravel QueryBuilder: https://github.com/illuminate/database/blob/master/Query/Builder.php