CODEWITHSUNDEEP
phpmysqlcodeigniter
Cecatrix
Cecatrix

Reputation: 151

Codeigniter: INSERT multiple values, UPDATE if already exists

I have a codeigniter query that Insert three foreign keys and 1 amount.

This is my query in the model:

public function create()
{
  $insert_data = array(
    'student_id' => $this->input->post('feestudentstudent'),
    'schoolyear_id' => $this->input->post('feestudentschoolyear'),
    'feetype_id' => $this->input->post('feestudentfeetype'),
    'feestudent_amount' => $this->input->post('feestudentamount')
  );
  $status = $this->db->insert('tbl_feestudent', $insert_data);
}

What I want is if the following three columns inserted is the same, the student_id, schoolyear_id and feetype_id values exists in the same row it will not insert a new row and the amount will just add to the amount of the existing row.

Take Note: It's okay that student_id, and schoolyear_id is the same, what I want is if all the three including feetype_id is the same it will add the amount.

Upvotes: 1

Views: 3608

Answers (3)

Kundan Prasad
Kundan Prasad

Reputation: 576

//MODEL    
public function upinsert($tabel,$data){
        $update='';
        $separator='';
        foreach ($data as $key => $value) {
          $update.=$separator." `$key` = '$value' ";
          $separator=',';
        }
        $sql = $this->db->insert_string($tabel, $data) . ' ON DUPLICATE KEY UPDATE '.$update;
        $this->db->query($sql);
        return $this->db->insert_id();
 }



//Your controller
public function create(){
  $insert_data = array(
    'student_id' => $this->input->post('feestudentstudent'),
    'schoolyear_id' => $this->input->post('feestudentschoolyear'),
    'feetype_id' => $this->input->post('feestudentfeetype'),
    'feestudent_amount' => $this->input->post('feestudentamount')
  );
  $status = $this->_model_name_here->upinsert('tbl_feestudent', $insert_data);
}

Upvotes: 1

sekaraja
sekaraja

Reputation: 177

before create data you check the table

    public function create()
    {
    $checkresult = $this->checkexist('tbl_feestudent',array('student_id'=>$this->input->post('feestudentstudent'),'schoolyear_id'=>$this->input->post('feestudentschoolyear'),'feetype_id'=>$this->input->post('feestudentfeetype')));
      $insert_data = array(
        'student_id' => $this->input->post('feestudentstudent'),
        'schoolyear_id' => $this->input->post('feestudentschoolyear'),
        'feetype_id' => $this->input->post('feestudentfeetype'),
        'feestudent_amount' => $this->input->post('feestudentamount')
      );
if($checkresult){
$this->db->where('student_id',$this->input->post('feestudentstudent'));
      $status = $this->db->update('tbl_feestudent', $insert_data);
    }else{
      $status = $this->db->insert('tbl_feestudent', $insert_data);
}

    public function checkexist($table,$where){
$this->db->select('*');
$this->db->select($table);
$this->db->where($where);
return $this->db->get()->result_array();
}

Upvotes: 0

Bakti Wijaya
Bakti Wijaya

Reputation: 477

you can put this code on your model

public function isExists($key,$valkey,$tabel)
{
    $this->db->from($tabel);
    $this->db->where($key,$valkey);
    $num = $this->db->count_all_results();
    if($num == 0)
    {
        return FALSE;
    }else{
        return TRUE;
    }
}

and in your controller,you can access those function,so it would be like

public function create()
{
   $student_id = $this->input->post('feestudentstudent');

   $insert_data = array(
  'student_id' => $this->input->post('feestudentstudent'),
  'schoolyear_id' => $this->input->post('feestudentschoolyear'),
  'feetype_id' => $this->input->post('feestudentfeetype'),
  'feestudent_amount' => $this->input->post('feestudentamount')
  );

  $update_data = array(
  'schoolyear_id' => $this->input->post('feestudentschoolyear'),
  'feetype_id' => $this->input->post('feestudentfeetype'),
  'feestudent_amount' => $this->input->post('feestudentamount')
  );

  //call the function
  $check = $this->(your model)->isExists('student_id',$student_id,'tbl_feestudent');

  if($check)
  {
    $this->db->where('student_id',$student_id);
    $this->db->update('tbl_feestudent', $update_data); 
  }
  else
  {
    $this->db->insert('tbl_feestudent', $insert_data);
  }

}

Hope this helps :D

Upvotes: 0

Related Questions