Codeigniter: INSERT multiple values, UPDATE if already exists

Question

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.

You should use ON DUPLICATE KEY UPDATE dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html - just write your own sql query. — Ivan Bolnikh
– Ivan Bolnikh, Commented Jul 9, 2017 at 12:07
Does this apply on same foreign keys? or is it applicable only in same Primary keys? — Cecatrix
– Cecatrix, Commented Jul 9, 2017 at 12:08
"f you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY" - so Primary or unique foreign key — Ivan Bolnikh
– Ivan Bolnikh, Commented Jul 9, 2017 at 12:10
But what if for example, I want to insert an amount in the same schoolyear_id with same student_id but it only checks if the feetype_id exists is it possible? — Cecatrix
– Cecatrix, Commented Jul 9, 2017 at 12:13
You should create unique index on (student_id, schoolyear_id and feetype_id). On insert when mysql get error on duplicate key - it just update non unique keys values. — Ivan Bolnikh
– Ivan Bolnikh, Commented Jul 9, 2017 at 12:18

Kundan Prasad · Accepted Answer · 2017-07-10 06:59:51Z

1

//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);
}

edited Jul 10, 2017 at 6:59

answered Jul 10, 2017 at 5:31

Kundan Prasad

5665 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bakti Wijaya · Accepted Answer · 2017-07-09 12:32:02Z

0

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

answered Jul 9, 2017 at 12:32

Bakti Wijaya

4871 gold badge7 silver badges24 bronze badges

1 Comment

Cecatrix Over a year ago

Thanks for the idea, I will try this :)

sekaraja · Accepted Answer · 2017-07-10 05:52:12Z

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();
}

Collectives™ on Stack Overflow

Codeigniter: INSERT multiple values, UPDATE if already exists

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related