views:

24

answers:

0

Hi there,

I build this little helper class to try class chaining in PHP to build SQL (atm very simple SELECTs) queries very easily. Any ideas or complaints about it, or tips for the future?

 class SQLQueryBuilder{

    public $query;
    public $parameters = array();
    const SELECT = 'SELECT ';
    const FROM = ' FROM ';
    const WHERE = ' WHERE ';

    public function __construct()
    {
        return $this;
    }

    public function select($values)
    {
        $this->query .= self::SELECT;

        if(is_string($values))
        {
            $this->query .= $values;
            return $this;
        }

        $i = 1;
        $count = count($values);
        foreach($values as $value)
        {
            if($i < $count)
            {
                $this->query .= $value . ', ';
            }
            else
            {
                $this->query .= $value;
            }
            $i++;
        }

        return $this;
    }

    public function from($name)
    {
        $tmp = self::FROM . $name;
        $this->query .= $tmp;

        return $this;
    }

    public function where($conditions)
    {
         $count = count($conditions);
         $i = 1;

         $this->query .= self::WHERE;

            foreach($conditions as $name => $condition)
            {
                $formatted_name = ':'.$name;
                $this->parameters[$formatted_name] = $condition;

                if($i < $count)
                {
                    $this->query .= $name.'= '.':'.$name.' ';
                    //$this->query .= 'bindValue = '.$condition.' '; (only for debug)
                    $this->query .= 'AND ';
                }
                else
                {
                    $this->query .= $name.'= '.':'.$name.' ';
                    //$this->query .= 'bindValue = '.$condition.' ';
                    $this->query .= ' ';
                }
                $i++;
            }

        return $this;
    }

Usage (Example):

include_once('db/SQLQueryBuilder.php');

$q = new SQLQueryBuilder;
 $where_data = array('id' => '42', 'name' => 'Herr Fiotzik');
 $select_data = array('id','name','house');
 $q->select($select_data)->from('MyModel')->where($where_data);
 echo $q->query;
 echo '<br />';
 echo '<br />';
 echo '<br />';
 print_r($q->parameters);

Outputs:

SELECT id, name, house FROM MyModel WHERE id= :id AND name= :name

Array ( [:id] => 42 [:name] => Herr Fiotzik )