How would i Build a mysql query through a set of PHP OOP methods?

Question

I want to be able to do something like this:

$table_object->getRows()->where($wer)->or($or)->orderBy('field', 'DESC');

If i were sure that all the methods will be called each time and in that order, then it would be simple and i can return an instance of the object itself on each method call so that the query gets build and finally executed at orderBy method. However I want the class to be able to also execute queries like so:

$table_object->getRows()->where($wer);

The following code would work for the first code example (ie when all methods are called) but not with the second one where only method where is called after getRows. It only returns an instance of itself.

class DatabaseTable extends Database
{ 
 protected $table_name;
 protected $query;

 public function getRows()
 {
  return ($this instanceof self)? $this : false;
 }

 public function where(array $where)
 {

  foreach ($where as $field => $value){
   $w[] = $field . ' = "' . $this->escapeString($value) . '"';
  }

  $this->query = "SELECT * FROM {$this->table_name} WHERE " . join($w, ' AND '));

  return $this;
 }

 public function or(array $Clause)
 {
  foreach ($clause as $field => $value){
   $o[] = $field . ' = "' . $this->escapeString($value) . '"';
  }

  $this->query .= join($w, ' AND ');

  return $this; 
 }

 public function orderBy($field, $type)
 {
  $this->query .= " ORDER BY $field $type ";
  $this->executeQuery($this->query); 
 }

}

Ignore all minor errors - (i didnt check if it worked for sure, but it should.) how can I achieve this?

Answer 1

Don't go through that hassle of building your own when you can use Doctrine

$q = Doctrine_Query::create()
    ->from('User u')
    ->leftJoin('u.Phonenumbers p');

or Propel

$c = new Criteria();
$c->add(AuthorPeer::FIRST_NAME, "Karl");
$c->add(AuthorPeer::LAST_NAME, "Marx", Criteria::NOT_EQUAL);
$authors = AuthorPeer::doSelect($c);

or Zend_Db_Query.

  $select = $db->select()
               ->from(array('p' => 'products'),
                      array('product_id', 'product_name'))
               ->join(array('l' => 'line_items'),
                      'p.product_id = l.product_id');

If they don't suit you for whatever reason, you can use them as a starting point on how to roll your own.

Answer 2

You might want to look at Zend Framework's Zend_Db_Select component, which provides an OO interface to SQL queries. Zend_Db also provides a number of other useful database functions.

Answer 3

As others have mentioned you shouldn't reinvent the wheel when a component already exists that can do what you want (Zend_Db_Select).

But if you still want to create your own, then one possibility is to include a Select($fields) method that is appended to the end of your query and that can take a list of fields to select or "*" for all. This select() method would be what actually executes the sql code for you. Yes, I know that it wouldn't look exactly like SQL in that the select would be at the end, but it is an easy solution to implement.

As an aside, what you are trying to do is build up a fluent interface or DSL, so it might be worth while to read up a little on them, to be see how some implementations are constructed.