Why would one want to write MySQL queries "programmatically like active record?"

I'm working with the TinyMVC framework, and it allows one to "build MySQL queries programmatically, much like active record." The example is embedded here (relevant TinyMVC documentation):

class Members_Model extends TinyMVC_Model
{
  function get_members()
  {
    $this->db->select('foo,bar,baz'); // set selected columns
    $this->db->from('mytable');  // set from what table(s)
    $this->db->where('foo','test'); // where foo='test'
    $this->db->orwhere('foo=? and bar=?',array('test','test2')) // where foo='test' and bar='test2'
    $this->db->join('jointable','mytable','jointable.foo=mytable.foo'); // join tables on (optional) condition
开发者_JAVA技巧    $this->db->in('mycolumn',$elements,$islist,$prefix) // IN clause: column, elements (comma-separated or array), $list=boolean is list or array, $prefix: AND|OR
    $this->db->orderby('ordercolumn'); // order by column(s)
    $this->db->groupby('groupbycolumn'); // group by column(s)
    $this->db->limit($limit,$offset); // query limit, optional offset
    $this->db->query();
    while($row = $this->db->next()) {
      $rows[] = $row;
    }
    return $rows;
  }
}

How is this different or better than the writing the SQL query outright:

SELECT foo, bar, baz
FROM mytable
WHERE...

---

The benefit is that you can have interdependent functions in your controller that can build on your query without having to worry about the order of your SQL. You can have conditional logic to use certain active record manipulations on one query and then simply run it when it's fully populated.

CodeIgniter's active record implementation is extremely useful. I imagine TinyMVC's is very similar.

---

The codeigniter website gives the following reason

Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.

---

it is an opinion (my)... you can play with part of the query qithout handling long string. for example:

if(){
  $this->db->where($a,$b);
  $this->db->where($c,$d);
  $this->db->where($e,$f);
}else{
  $this->db->where($g,$h);
  $this->db->where($i,$j);
  $this->db->where($k,$l);
}

writing the above statement in one string is not very nice coding and hard for maintenance.
And in addition to that, you can than generate cross database queries (helps for migration and in shelf products). that is only one good reason...

---

It is not really different, it's more to make it easier for you, the programmer. Because if you write the query yourself, you have to worry about security yourself (mysql injection for example) using their model, they do that for you. You only need to put your variables in and done.

---

It is way easier to be consistent in your code. And easy to always escape strings etc when doing inserts/updates. Security is important.

---

<troll> Because some people are allergic to SQL and want to shoehorn objects into relational operations.

Security ? Use prepared statements. Portability ? Have a list of queries per DBM. And if you have to dynamically generate a query, you may have to rethink how you separate your database layer from your application layer. </troll>

The advantage of those ways of doing database queries is all this code can be generated quickly using some framework. Which enables you to prototype applications very fast which you'll need often. And you don't have to learn vendor specific SQL when going from one DBM to another (google "Oracle Limit" to see an example of what I mean).