# Is having preset queries prone for disaster?

Edit Just did some further reading around the website and have come to the conclusion that this method leads to Leaky Abstraction, sorry for wasting peoples time. Time to head back to the drawing board.

Situation Context

I currently develop on my own, building codeigniter cms type websites for clients that want to manage products and run a cart like system. I have my boss who does the designs, sends me the psd and says get on with it, putting in his 2 cents in every now and then as he finds things he wants done differently.

I work with no other programmers so have no one to critique my methods, and with this being my first development role I would imagine i'm missing out on a lot of important feedback.

Anywho, the situation is this, I find myself doing the same types of queries often and have functions all over the show doing similar, but slightly different things. Some will be selecting all with a limit and others with an order by. So I decided maybe it wouldn't be a bad idea to set up a model that does most of my selecting based on the parameters I send it. The following is what I have mocked up over the last hour, before I go ahead with fine tuning it I wanted to pick some more experienced programmers brains to find out if this path is a flawed/dangerous one and i'm just not seeing it or if this isn't a bad idea and should continue.

An Example Controller

public function query_helper(){

$table = 'entry';$where = array();
$where[] = array('where' => 'entry_name', 'equals' => 'Up And Running!');$where[] = array('where' => 'author_id', 'equals' => 1);
$order_by = array('entry_name', 'DESC');$limit = array(1,0);

$data['record'] =$this->query_model->get($table,$where, $orwhere,$order_by, $limit);$this->load->view('pages/query_helper', $data); }  The Model class Query_model extends CI_Model { /** * This funtion selects an individual record based on the paramaters sent and returns * only one result * * @return array * @author Oliver **/ function get($table, $where,$orwhere,$order_by,$limit){

for($i = 0;$i < count($where);$i++){

$this->db->where($where[$i]['where'],$where[$i]['equals']); } for($i = 0; $i < count($orwhere); $i++){$this->db->or_where($orwhere[$i]['where'],$orwhere[$i]['equals']);
}

if($order_by){ // Variable must be array with condition followed by ASC or DESC$this->db->order_by($order_by[0],$order_by[1]);
}

if($limit){ // Variable must be array with limit followed by start$this->db->limit($limit[0],$limit[1]);
}

$query =$this->db->get($table); return$query->row();
}

/**
* This funtion selects all records based on the paramaters sent, including the limit,
* and returns only one result
*
* @return array
* @author Oliver
**/
function get_all($table,$where, $orwhere,$order_by, $limit){ for($i = 0; $i < count($where); $i++){$this->db->where($where[$i]['where'],$where[$i]['equals']);
}

for($i = 0;$i < count($orwhere);$i++){

$this->db->or_where($orwhere[$i]['where'],$orwhere[$i]['equals']); } if($order_by){
// Variable must be array with condition followed by ASC or DESC
$this->db->order_by($order_by[0], $order_by[1]); } if($limit){
// Variable must be array with limit followed by start
$this->db->limit($limit[0], $limit[1]); }$query = $this->db->get($table);

return $query->result(); } /** * This funtion inserts a record based on the paramaters sent. * * @return bool * @author Oliver **/ function insert($table, $data){ if($this->db->insert($table,$data)){
return true;
}
else{
return false;
}
}

/**
* This funtion updates a record based on the paramaters sent.
*
* @return bool
* @author Oliver
**/
function update($table,$data, $where,$orwhere){

for($i = 0;$i < count($where);$i++){

$this->db->where($where[$i]['where'],$where[$i]['equals']); } for($i = 0; $i < count($orwhere); $i++){$this->db->or_where($orwhere[$i]['where'],$orwhere[$i]['equals']);
}

if($this->db->update($table, $data)){ return true; } else{ return false; } } /** * This funtion deletes a record based on the paramaters sent. * * @return bool * @author Oliver **/ function delete($table, $where,$orwhere){

for($i = 0;$i < count($where);$i++){

$this->db->where($where[$i]['where'],$where[$i]['equals']); } for($i = 0; $i < count($orwhere); $i++){$this->db->or_where($orwhere[$i]['where'],$orwhere[$i]['equals']);
}

if($this->db->delete($table)){
return true;
}
else{
return false;
}
}


}

Abstractions rarely result in optimizations, but that doesn't mean they aren't useful. Whether this a good idea or not depends upon whether you have a large number of tables that can be treated the same, and whether that helps you.

In general it's not a good idea not because there are cases where it doessn't do what you want, but because it will add complextity for little gain. If your tables are relatively stable, you could use the same (or very similar) code to autogenerate stored procedures, which you then call directly -- you end up with more code, but it's easier to understand.