Automatically filling in sql column data after referencing a substring of another column-CodePudding

Currently I'm using Mysql and CodeIgniters MVC framework to fill in my data. The table logs every status change that has been made and when it was made. This is what the database currently looks like:

I've added new columns in the table called status_from and status_to, where I want these columns to take the substring from action column.

But now how do I display it in my database with my following code:

Controller class:

public function status($status){ 
        $statusar = array('D'=>'Draft','N'=>'Unpublish','Y'=>'Publish','U'=>'Action','L'=>'Unlisted','S'=>'Sold','T'=>'Let');
        if($this->input->post('id')){
            foreach($this->input->post('id') as $key => $id):
            $check = $this->listings_model->loadlisting_check($id);
            $log = "Listing website status changed from ". $statusar[$check->status]." to ".$statusar[$status].". The listing ID is #".$id.".";
            $this->logs_model->insert_log(array('refno'=>$check->refno,'action'=>trim($log)));
            $data=array('status'=>$status);  
            if($status == 'T' || $status == 'Y' || $status == 'S'){
                $pub = 1;
            }else{
                $pub =0;
            }
            $this->listings_model->lpupdate(array('property_publish'=>$pub),$id);

            endforeach; 
        } 
        return true;
    }

listings_model:

function loadlisting_check($id)
    {
        $this->db->select("refno, status, archive");
        $id=$this->db->escape_str($id);
        $cond=array("$this->table_name.$this->primary_key"=>$id);
        $this->db->where($cond); 
        $this->db->from($this->table_name);
        $query = $this->db->get();
        return $query->row();
     }

logs_model:

public function insert_log($log)
    {
        $log['agent_id'] = $this->session->userdata('clientsessuserid');
        $log['logtime'] = date('Y-m-d H:i:s');
        $this->db->insert($this->table_name, $log);
        return true;
    }

Basically I want to fill my status_from column with $statusar[$check->status] and status_to column with $statusar[$status] when every new entry is made

CodePudding user response：

you can pass the variables you want to add in the array in insert_log method parameter with proper key matched with column name in your table

 public function status($status){ 
            $statusar = array('D'=>'Draft','N'=>'Unpublish','Y'=>'Publish','U'=>'Action','L'=>'Unlisted','S'=>'Sold','T'=>'Let');
            if($this->input->post('id')){
                foreach($this->input->post('id') as $key => $id):
                $check = $this->listings_model->loadlisting_check($id);
                $log = "Listing website status changed from ". $statusar[$check->status]." to ".$statusar[$status].". The listing ID is #".$id.".";
                $this->logs_model->insert_log(array('refno'=>$check->refno,'action'=>trim($log) , 'status_from'=>$statusar[$check->status] ,'status_to'=>$statusar[$status]));
                $data=array('status'=>$status);  
                if($status == 'T' || $status == 'Y' || $status == 'S'){
                    $pub = 1;
                }else{
                    $pub =0;
                }
                $this->listings_model->lpupdate(array('property_publish'=>$pub),$id);
    
                endforeach; 
            } 
            return true;
        }

and you will not need to add anything else to your model
I hope it solves the problem