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?

Sorry if I sound like i'm trolling, but $table_object->getRows()->Where($wer)->OR($or)->orderBy('field', 'DESC'); You basically wrote the entire query there. Why not just write "SELECT * WHERE {$wer} OR {$or} ORDER BY field DESC" ._.? — Warty, Feb 21 '10 at 18:30
@ItzWarty because he likely wants an DB-agnostic OOP wrapper for this. — Gordon, Feb 21 '10 at 18:35
I have to agree with ltzWarty. There is no good reason to take a decent domain specific language and turn it into an ugly OOP mess. — Imbue, Feb 21 '10 at 18:47
@Imbue If you need to change the database for whatever reason, you don't have to bother about compatibility of the used SQL dialect. I'd agree this rarely happens, but rarely is not never. Also, not all developers know SQL and feel much more comfortable writing in their prefered language, whichever it may be. Building dynamic queries is also much easier and maintainable when you don't have to mess with manipulating a SQL string, but an object. A generic query interface can also be used against any datasource, not RDBMs. .NETs LINQ is an example for this. — Gordon, Feb 21 '10 at 21:58

Gordon · Accepted Answer · 2010-02-21T18:34:43.523

6

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.

edited Feb 21 '10 at 18:34

answered Feb 21 '10 at 18:29

Gordon

312,688
75
539
559

2

@David these are from the respective frameworks' documentation – Gordon Feb 21 '10 at 18:40
My purpose here was more to learn how its done rather then to recreate.. I ll have a look at those. – shxfee Feb 21 '10 at 18:48
1

If you immediately decided to start from scratch you learn how _you_ would do it and in all likelihood not how it's done "properly" (no offense, we're all in the same boat). Do some research, look at what others have done before ..then, maybe, roll you own implementation. Relax, and stand on the shoulders of giants for a while =] – VolkerK Feb 21 '10 at 19:07
1

I'm not sure why some developers are hesitant to write raw SQL queries. The examples above are a terrible mess. – Johannes Gorset Feb 21 '10 at 19:53
@FRKT They are not exactly pretty, but there are cases, when they are much less of a mess than a couple dozen lines of SQL code that needs criteria added and/or removed on the fly. – Gordon Feb 21 '10 at 22:01

score 4 · Answer 2 · answered Feb 21 '10 at 18:30

4

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.

answered Feb 21 '10 at 18:30

David Snabel-Caunt

57,804
13
114
132

:/ never occurred to me to have a look in there. – shxfee Feb 21 '10 at 18:47

score 0 · Answer 3 · answered Feb 21 '10 at 18:46

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.

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

3 Answers3