My Codeigniter CRUD Model

| November 5, 2009 | Reply

I’ve modified this model to be more portable by expecting an explicit database name.My_Model.php as requested in comments.

< ?php /* * This model extends the default model. All Models built extending this model * will inherit these functions as well as CI's native model. Common DB code * should be placed here. */ class MY_Model extends Model{ function MY_Model(){ parent::Model(); // This allows us to connect to multiple databases instead of // autoloading a single one. $this->defaultdb=$this->load->database(‘default’, TRUE);
$this->tminusdb=$this->load->database(‘tminus’, TRUE);
$this->rxmmdb=$this->load->database(‘rxmm’, TRUE);
}
}//end class

{/codecitation}

Use as such:

{codecitation}
$userdata=array(
‘id_selector’=>’uid’,
‘id_value’=>$uid,
‘status’=>’INACTIVE’,
‘modifiedby’=>$this->session->userdata["username"]
);

$this->Mcommon->modRecord(‘defaultdb’,’users’,$userdata);
{/codecitation}

{codecitation}

< ?php class Mcommon extends MY_Model{ /* * This is a CRUD (Create, Read, Update, Delete) model * that can be used to interface with a database. All database * operations should go through this model for uniformity. */ function Mcommon(){ parent::MY_Model(); } function _default($defaults, $options) { return array_merge($defaults, $options); } function addRecord($db,$table,$options = array()){ $this->$db->insert($table,$options);
// Return the ID of the inserted row, or false if the row could not be inserted
return $this->$db->insert_id();
}

function modRecord($db,$table,$options = array()){
/*
* We’ll probably need to send a unique ID to determine the record modified.
* This function has ‘id_selector’ and ‘id_value’ for this purpose.
*/
$sql=”SELECT column_name FROM information_schema.columns WHERE table_name=’$table’”;
$query=$this->$db->query($sql);
foreach ($query->result_array() as $row)
{
// build new array of column names.
$columns[]=$row['column_name'];
}//end foreach

// Use the $columns array we built earlier to set the clause.
$qualificationArray = $columns;
foreach($qualificationArray as $qualifier){
if(isset($options[$qualifier])) $this->$db->set($qualifier, $options[$qualifier]);
}//end foreach

if(isset($options['id_selector'])){
if(isset($options['id_value'])){
$this->$db->where($options['id_selector'],$options['id_value']);
}//end if
}//end if

// remove the id qualifiers.
unset($options['id_selector']);
unset($options['id_value']);

//Execute the query
$this->$db->update($table,$options);

}//end function

function delRecord($db,$table,$options = array()){

$sql=”SELECT column_name FROM information_schema.columns WHERE table_name=’$table’”;
$query=$this->$db->query($sql);
foreach ($query->result_array() as $row)
{
// build new array of column names.
$columns[]=$row['column_name'];
}//end foreach

// Use the $columns array we built earlier to set the WHERE clause.
$qualificationArray = $columns;
foreach($qualificationArray as $qualifier){
if(isset($options[$qualifier])) $this->$db->where($qualifier, $options[$qualifier]);
}//end foreach

$this->$db->delete($table);
}

function getRecords($db,$table,$options = array()){

// This allows us to pass the fields we want to return.
if(isset($options['fields'])){
foreach($options['fields'] as $field){
$this->$db->select($field);
}//end foreach
}//end if

if(isset($options['joins'])){
foreach($options['joins'] as $join){
$jointable=$join['table'];
$join1=$join['join1'];
$join2=$join['join2'];
$jointype=$join['jointype'];
$this->$db->join(“$jointable”,”$join1 = $join2″, “$jointype”);
}//end foreach
}//end if

if(isset($options['likes'])){
foreach($options['likes'] as $like){
$selector=$like['like_selector'];
$value=$like['like_value'];
$this->$db->like(“$selector”,”$value”);
}//end foreach
}//end foreach
// This block will take any option keys that do not have a table
// explicitly set, and will append the given table to them.
$keywords=array(‘limit’,’offset’,’sortBy’,’sortDirection’,’joins’);
$qualtables=”table_name=’$table’”;
if(!empty($options)){
foreach ($options as $key=>$value){
if (!in_array($key,$keywords)){
if (!preg_match (‘/[^a-z]/i’, $key)) {
$options[$table.'.'.$key]=$options[$key];
unset($options[$key]);
}else{
//Note any tables other than $table that we want to pull data from.
//This way we can limit the qualification array to only the table
//data that we actually need.
$qualtables.=” OR table_name=’”.substr($key, 0, strrpos($key, ‘.’)).”‘”;
}
}//end if
}//end foreach
}//end if

// This pulls the column names from our table and dynamically ads them as qualifiers.
// This allows us to set WHERE clauses by passing ‘column’=>’field’ in the $options
// array without explicity setting allowable columns. It’s an extra query,
// but it makes this code much more portable.
$sql=”SELECT table_name,column_name FROM information_schema.columns WHERE $qualtables”;
$query=$this->$db->query($sql);
foreach ($query->result_array() as $row)
{
// build new array of column names.
$columns[]=$row['table_name'].’.’.$row['column_name'];
}//end foreach

// Use the $columns array we built earlier to set the WHERE clause.
$qualificationArray = $columns;

foreach($qualificationArray as $qualifier){
if(isset($options[$qualifier])) $this->$db->where($qualifier, $options[$qualifier]);
}//end foreach

// Check for nonstandard records, (Greater and Less than)
// The previous check won’t match the qualification array because
// the qualification key contains the operators.
if(!empty($options)){
foreach ($options as $key=>$value){
if (preg_match (‘/[<>!]/i’, $key)){
$this->$db->where($key,$value);
}//end if
}//end foreach
}//end if

// Handle LIMIT, OFFSET, and ORDER.
if(isset($options['limit']) && isset($options['offset'])) $this->$db->limit($options['limit'], $options['offset']);
else if(isset($options['limit'])) $this->$db->limit($options['limit']);
if(isset($options['sortBy'])) $this->$db->order_by($options['sortBy'], $options['sortDirection']);

// Issue final query.
$query = $this->$db->get($table);

if($query->num_rows() == 0) return false; // No records to return.
return $query->result_array();

}//end function

function getNumRecords($db,$table,$options){

// This is basically a copy of the getRecords function, but
// it ignores the limit and offset, and returns just the total number
// of results that getRecords WOULD have returned. It takes
// the same options array.

// This allows us to pass the fields we want to return.
if(isset($options['fields'])){
foreach($options['fields'] as $field){
$this->$db->select($field);
}//end foreach
}//end if

if(isset($options['joins'])){
foreach($options['joins'] as $join){
$jointable=$join['table'];
$join1=$join['join1'];
$join2=$join['join2'];
$jointype=$join['jointype'];
$this->$db->join(“$jointable”,”$join1 = $join2″, “$jointype”);
}//end foreach
}//end if

if(isset($options['likes'])){
foreach($options['likes'] as $like){
$selector=$like['like_selector'];
$value=$like['like_value'];
$this->$db->like(“$selector”,”$value”);
}//end foreach
}//end foreach
// This block will take any option keys that do not have a table
// explicitly set, and will append the given table to them.
$keywords=array(‘limit’,’offset’,’sortBy’,’sortDirection’,’joins’);
$qualtables=”table_name=’$table’”;
if(!empty($options)){
foreach ($options as $key=>$value){
if (!in_array($key,$keywords)){
if (!preg_match (‘/[^a-z]/i’, $key)) {
$options[$table.'.'.$key]=$options[$key];
unset($options[$key]);
}else{
//Note any tables other than $table that we want to pull data from.
//This way we can limit the qualification array to only the table
//data that we actually need.
$qualtables.=” OR table_name=’”.substr($key, 0, strrpos($key, ‘.’)).”‘”;
}
}//end if
}//end foreach
}//end if

// This pulls the column names from our table and dynamically ads them as qualifiers.
// This allows us to set WHERE clauses by passing ‘column’=>’field’ in the $options
// array without explicity setting allowable columns. It’s an extra query,
// but it makes this code much more portable.
$sql=”SELECT table_name,column_name FROM information_schema.columns WHERE $qualtables”;
$query=$this->$db->query($sql);
foreach ($query->result_array() as $row)
{
// build new array of column names.
$columns[]=$row['table_name'].’.’.$row['column_name'];
}//end foreach

// Use the $columns array we built earlier to set the WHERE clause.
$qualificationArray = $columns;
foreach($qualificationArray as $qualifier){
if(isset($options[$qualifier])) $this->$db->where($qualifier, $options[$qualifier]);
}//end foreach

// Check for nonstandard records, (Greater and Less than)
// The previous check won’t match the qualification array because
// the qualification key contains the operators.
if(!empty($options)){
foreach ($options as $key=>$value){
if (preg_match (‘/[<>!]/i’, $key)){
$this->$db->where($key,$value);
}//end if
}//end foreach
}//end if
$numrecords=$this->$db->count_all_results($table);
return $numrecords;
}

function getRecordsById($db,$table,$id,$value,$add_blank=false)
{
$sql=”SELECT DISTINCT $id,$value FROM $table ORDER BY $value ASC”;
$query=$this->$db->query($sql);
$result=$query->result_array();
if($add_blank){
$result=$this->array_to_select($result,$id,$value,’ ‘);
}else{
$result=$this->array_to_select($result,$id,$value);
}
return $result;
}//end function

}//end class
?>

Tags: ,

Category: Uncategorized

About the Author ()