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

user_16572951 0
Viewed 42

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:

enter image description here

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

codeigniter mysql php
1 Answers

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

user_150430400
Related