I’m trying to design an SQL builder in PHP. I’m trying to make it so that the system could work with different database systems (e.g. Oracle, MySQL, MSSQL). Here’s what I’ve got so far.

First, I have an interface named ISelectQuery which defines a contract which all Select query builders must adhere to:

interface ISelectQuery{
    public function addTables($tables);
    public function addColumns($columns);
    public function addWhere($column,$operator,$value,$bind = null);
    public function addOrder($col,$dir);
    public function validate();
    public function toString();
    public function getTables();
    public function getColumns();
    public function getWhere();
    public function getOrder();
    public function getBinds();
}

Next, I have a class named OracleSelectQuery which is my query builder for Oracle. In the future there could also be MysqlSelectQuery.

class OracleSelectQuery implements ISelectQuery{
    const EQUALS = '=';
    const NOTEQUALS = '!=';
    const LIKE = 'LIKE';
    const ASC = 'ASC';
    const DESC = 'DESC';

    private $tables = array();
    private $columns = array();
    private $where = array();
    private $order = array();
    private $binds = array();

    //add an array of tables to the query
    public function addTables($tables){
        $this->tables = array_merge($this->tables, $tables);
        return $this;
    }

    //add an array of columns to the query
    public function addColumns($columns){
        $this->columns = array_merge($this->columns, $columns);
        return $this;
    }

    //add a where clause to the query
    public function addWhere($column,$operator,$value,$bind = null){
        $where = array($column,$operator,$value,$bind);
        $this->where[] = $where;
        return $this;
    }

    //add some ordering to the query
    public function addOrder($col,$dir){
        $order = array($col,$dir);
        $this->order[] = $order;
        return $this;
    }

    public function validate(){
        //can't be a valid query if tables aren't defined
        if(empty($this->tables)){
            throw new Exception('Tables not defined.');
        }
        //can't be a valid query if columns aren't defined
        if(empty($this->columns)){
            throw new Exception('Columns not defined.');
        }
        //search through each where clause to see if they are valid
        foreach($this->where as $where){
            if(!in_array($where[1],array($this::EQUALS, $this::NOTEQUALS, $this::LIKE))){
                throw new Exception($where[1].' is not a valid WHERE clause');
            }
        }
        //search through each order by clause to see if they are valid
        foreach($this->order as $order){
            if(!in_array($order[1],array($this::ASC, $this::DESC))){
                throw new Exception($order[1].' is not a valid ORDER BY clause');
            }
        }

        return $this;
    }

    public function toString(){     
        //add the columns to the query
        $sql = 'select '.implode(', ',$this->columns).' from ';

        //add the tables
        $sql .= implode(', ',$this->tables).' ';

        //add the where clauses (if where clauses were added)
        if(!empty($this->where)){
            $sql.= 'where ';

            //loop through each where clause
            foreach($this->where as $where){
                //where[0] will be the column, where[1] will be the comparison
                $sql.= $where[0].' '.$where[1].' ';

                //if where[2] has a '?' in it, it must be a bind
                if(strstr($where[2],'?') !== false){
                    //give the bind variable a unique name, by counting the number of binds already created
                    $bindName = ':bind'.count($this->binds);

                    //replace the '?' with the bind variable name
                    $sql .=' '.str_replace('?',$bindName,$where[2]);

                    //add the bind variable name and value to the list of binds - this can be used by the database connection
                    $this->binds[] = array($bindName,$where[3]);
                }else{
                    //if it's not a bind variable, add the simle value
                    $sql .= $where[2];
                }

                //don't forget to add the and at the end
                $sql .= ' and ';
            }

            //remove the last ' and '
            $sql = substr($sql,0,-5);
        }

        //add the order clauses (if they exist)
        if(!empty($this->order)){
            $sql .= ' order by ';

            //loop through each order clause and add it to the sql
            foreach($this->order as $order){
                $sql .= $order[0].' '.$order[1].', ';
            }

            //remove the last comma
            $sql = substr($sql,0,-2);
        }

        //return the finished query to the client
        return $sql;
    }

    //getters to allow client to inspect query later
    public function getTables(){
        return $this->tables;
    }

    public function getColumns(){
        return $this->columns;
    }

    public function getWhere(){
        return $this->where;
    }

    public function getOrder(){
        return $this->order;
    }

    public function getBinds(){
        return $this->binds;
    }
}

You can then use the following code to generate SQL:

//create a new select query
$select = new OracleSelectQuery();
$sql = $select->addTables(array('table1','table2'))
                            ->addColumns(array('col1','col2'))
                            ->addWhere('col1',OracleSelectQuery::LIKE,'?','value')
                            ->addOrder('col1',OracleSelectQuery::ASC)
                            ->validate()
                            ->toString();
echo $sql;

My question is – is this a good implementation of the builder pattern? Is there any way you would improve it?