Extending Zend DB Table
For some time now I’ve been working on an administrative backend system. I quickly found the need to extend DB Table with more stuff than needed when I extended the Zend Framework, it’s mostly convenience functions designed to reduce repetitive code snippets.
If you have your own copy of ExtModel.php open you should have no problems walking this, we start below fetchAllKeyValue().
function cleanArray($arr){
return array_intersect_key($arr, array_combine($this->_cols, $this->_cols));
}
function insert(&$insert_arr){
return parent::insert($this->cleanArray($insert_arr));
}We clean the array and insert it, with this logic you can simply pass the POST array straight to the insert function without having to worry about extra key/values screwing up the insert.
function updateOne($update_arr, $id_value){
return parent::update($this->cleanArray($update_arr), "{$this->_primary[1]} = $id_value");
}Similar logic applies here. Note that the first primary key is used (usually ‘id’). Some convention over configuration, love it or hate it…
function exists($where){
return $this->fetchAll($where)->count() > 0 ? true : false;
}
function delete($where){
if($this->exists($where))
return parent::delete($where);
}
function deleteOne($id_value){
return $this->delete("{$this->_primary[1]} = $id_value");
}
function deleteFirst($where, $key = false){
$key = $key == false ? $this->_primary[1] : $key;
$id = $this->fetchAll($where)->current()->$key;
parent::delete("$key = '$id'");
return $id;
}Useful if we are too lazy to write delete(“id = $id_value”) all the time we want to delete something. deleteFirst() is the most advanced delete function, it will delete the first occurrence and can accept an arbitrary field, at the moment it’s just called with $where so the arbitrary stuff has not been tested yet.
function updateAll($update_arr, $field_value, $field_id){
if(is_array($update_arr)){
foreach($update_arr as $key => $val)
$result = $this->update(array($field_value => $val), "$field_id = '$key'");
}
}This one will update an array of entries, don’t use it with too many entries though as it makes a call for each one. It could be called with something like this: updateAll(array(1 => ‘ok’, 3 => ‘not_ok’), ‘status’, ‘id’).
function fetchOne($id, $as_arr = false){
$result = $this->fetchRow("{$this->_primary[1]} = $id");
return $as_arr ? $result->toArray() : $result;
}Again only used to be able to skip the where parameter, it will be a little shorter, same goes for the as_array parameter which can be debated as it will make the code less readable.
function fetchAllWhere($where = false, $operator = false){
return $this->fetchSomething($where, $operator, true);
}
function fetchRowWhere($where = false, $operator = false){
return $this->fetchSomething($where, $operator, false);
}
function fetchSomething($where = false, $operator = false, $all = true){
if($where != false){
$where_sql = $this->buildWhere($where, $operator);
if($all)
$result = $this->fetchAll($where_sql);
else
$result = $this->fetchRow($where_sql);
}
return $result;
}
function buildWhere($where, $operator = false){
$operator = $operator == false ? "= ?" : $operator;
$where_sql = array();
foreach($where as $field => $value){
if(in_array($field, $this->_cols))
$where_sql[] = $this->quoteInto($field, $operator, $value);
}
return $where_sql;
}
function quoteInto($field, $operator, $value){
switch($operator){
case 'LIKE%%':
$value = "%$value%";
$operator = "LIKE ?";
break;
default:
break;
}
return $this->getAdapter()->quoteInto("$field $operator", $value);
}Note that fetchAllWhere and fetchRowWhere are just frontends for fetchSomething with the goal of making the code more readable. At the moment quoteInto is not much but it can be easily extended to handle more options. Let’s look at an example of calling code to better understand what is happening here:
function search(){
$this->aclOrExit(2);
$post = $this->_request->getParams();
$this->assign('cur_acl', $this->getAclLevel());
$this->assign('users', $this->obj->fetchAllWhere($post, 'LIKE%%')->toArray());
return $this->fetch('search_result.tpl');
}This searchfunction will get the result of a form POST with only textfields it will match each field partially and return the result with a minimum of fuss.
Related Posts
Tags: PHP, zend framework