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?

I personally prefer DB->exec('sql string', $parametersArray); To much abstraction is a pain in the a**e. But thats just my opinion — Steve
– Steve, Commented Apr 30, 2014 at 13:19
Actually my class uses PDO I'm just trying to simplify select, insert, update and delete for later use. — Nergal
– Nergal, Commented Apr 30, 2014 at 13:25

Pinoniq · Accepted Answer · 2014-04-30 13:16:28Z

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

Alexander Kludt · Accepted Answer · 2014-04-30 13:07:12Z

1

What you are searching for is method chaining, also fuelphp (just like doctrine and others) is caching what you are sending in and builds the query after that:

public function from($table){
   $this->_table = $table;
   return $this; //this is the important part for chaining
}

public function where($key,$value){
   $this->where = array($key => $value);
   return $this;
}

public function andWhere($key,$value){
   if(!$this->where) $this->where = array();
   $this->where[$key] = $value;
   return $this;
}

public function getQuery(){
   //Build query here from what we stored before
   $query = '';
   ....
   return $query;
}

Oh well, what i forgot is what DB::get returns, an instance of what the class above is and executes that then:

public static function get(){
   return new Query(); //above functions are part of Query class
}

answered Apr 30, 2014 at 13:07

Alexander Kludt

8636 silver badges17 bronze badges

2 Comments

Pinoniq Over a year ago

ahh, just a few sec earlier then me :p

Alexander Kludt Over a year ago

but yours goes a little more into detail, so I would suggest marking yours as the answer.

Collectives™ on Stack Overflow

PHP Database class - select function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related