Insert and update in one query in mysql codeigniter

Question

I am new on codeigniter,I am doing the following things but I didn't get the solution.

Here is the following things one by one.

First I fetch the data from database and display in table format by foreach loop.
```
<?php 
```
$attributes = array('class' => 'form-horizontal','id'=>'update_form'); echo form_open('emp_hodm_update/update_hodm', $attributes); ?>
date Work Partner Director Time Task Status Action date;?>" class="" /> work;?>" class="" /> partner;?>" class="" /> director;?>" class="" /> time;?>" class="" /> task;?> status;?> Delete
By clicking add button it creates new rows SO I want to update the old data as well as new row data which is created by add buttond to insert into a table.
But I didn't get any idea how to do this.

Controller:

public function update_hodm(){ /* Checking the all validation of task form*/ //$this->form_validation->set_rules('date', 'Date', 'required'); $this->form_validation->set_rules('work[]', 'Types of Work', 'required'); //$this->form_validation->set_rules('partner[]', 'Worked With', 'required'); $this->form_validation->set_rules('director[]', 'Director', 'required'); $this->form_validation->set_rules('time[]', 'No Of Hours', 'required'); $this->form_validation->set_rules('task[]', 'Task Details', 'required'); $this->form_validation->set_rules('status[]', 'Task Status', 'required'); if ($this->form_validation->run()) { /* Taking the data from form*/ $todayDate = date('Y-m-d'); $work=$this->input->post('work'); $partner=$this->input->post('partner'); $director=$this->input->post('director'); $time=$this->input->post('time'); $task=$this->input->post('task'); $status=$this->input->post('status'); $count=count($this->input->post('work')); $data =array(); for($i=0; $i<$count; $i++) { $data[$i] = array( 'name' =>$this->session->userdata('emp_name'), 'date' =>$todayDate, 'work' =>$work[$i], 'partner' =>$partner[$i], 'director' =>$director[$i], 'time' =>$time[$i], 'task' =>$task[$i], 'status' =>$status[$i] ); } $add=$this->update->update_hodm($data,$todayDate); /* Display Success message if data updated successfully in database*/ if($add){ $this->session->set_flashdata('hodm_form',"All HODM Data Inserted Successfully."); $this->session->set_flashdata('hodm_form_class','alert-success'); }else{ /* Displaying the error message*/ $this->session->set_flashdata('hodm_form',"failed to add, Please Try again"); $this->session->set_flashdata('hodm_form_class','alert-danger'); } return redirect('home'); } else { $this->load->view('public/digital_hodm_view'); } }

Model:

public function update_hodm($data,$todayDate){ $this->db->where('date',$todaydate); $this->db->update_batch('task_form', $data,'date');//date is my table column name return true; }

Please help me to find the solution.

Thanks in advance

Why do you need to update the old data when add button is clicked? Just create an add function in controller and call that when add button is clicked — Sultan
– Sultan, Commented Aug 3, 2016 at 11:07
You dont actually mention what the problem is! Apart from But I didn't get any idea how to do this. In which case the solution is to hit the books — RiggsFolly
– RiggsFolly, Commented Aug 3, 2016 at 11:08
@Sultan you didn't get me.When i clicked on add button then it creates new row of same column blank . after filling the new row and changing in old row when i clicked the submit button then it goes into table with old data and new data — iazizkhan
– iazizkhan, Commented Aug 3, 2016 at 11:10

marc_s · Accepted Answer · 2016-08-06 09:34:18Z

Use below code for view see add one more column updated_at

View:

<?php $attributes = array('class' => 'form-horizontal','id'=>'update_form'); echo form_open('emp_hodm_update/update_hodm', $attributes); ?> <table class="table table-striped table-hover table-responsive"> <thead> <tr class=""> <th>date</th> <th>Work</th> <th>Partner</th> <th>Director</th> <th>Time</th> <th>Task</th> <th>Status</th> <th>Action</th> </tr> </thead> <tbody> <?php foreach($result as $row) { ?> <tr> <td> <input class="form-control" type="text" name="date[]" value="<?php echo $row->date;?>" class="" /> </td> <td> <input class="form-control" type="text" name="work[]" value="<?php echo $row->work;?>" class="" /> </td> <td> <input class="form-control" type="text" name="partner[]" value="<?php echo $row->partner;?>" class="" /> </td> <td> <input class="form-control" type="text" name="director[]" value="<?php echo $row->director;?>" class="" /> </td> <td> <input class="form-control" type="text" name="time[]" value="<?php echo $row->time;?>" class="" /> </td> <td> <textarea class="form-control" name="task[]" rows="2" id=""><?php echo $row->task;?></textarea> </td> <td> <textarea class="form-control" name="status[]" rows="2" id=""><?php echo $row->status;?></textarea> </td> <td> <a href="" class="btn btn-danger">Delete</a> <!-- Add This --> <input class="form-control" type="hidden" name="form_id_hidden[]" value="<?php echo $row->form_id;?>" </td> </tr> <?php } ?> </tbody> </table> <input class="btn btn-primary" type="submit" name="submit" value="submit"> <input class="btn btn-primary" type="submit" name="submit" value="add"> <?php echo form_close(); ?>

Controller :

 public function update_hodm(){ /* Checking the all validation of task form*/ //$this->form_validation->set_rules('date', 'Date', 'required'); $this->form_validation->set_rules('work[]', 'Types of Work', 'required'); //$this->form_validation->set_rules('partner[]', 'Worked With', 'required'); $this->form_validation->set_rules('director[]', 'Director', 'required'); $this->form_validation->set_rules('time[]', 'No Of Hours', 'required'); $this->form_validation->set_rules('task[]', 'Task Details', 'required'); $this->form_validation->set_rules('status[]', 'Task Status', 'required'); if ($this->form_validation->run()) { /* Taking the data from form*/ $todayDate = date('Y-m-d'); <!-- Add This --> $updatedDate = date('Y-m-d H:i:s'); // if not used H:i:s and there is no chnage in data update will return false $work=$this->input->post('work'); $partner=$this->input->post('partner'); $director=$this->input->post('director'); $time=$this->input->post('time'); $task=$this->input->post('task'); $status=$this->input->post('status'); <!-- Add This --> $form_id=$this->input->post('form_id_hidden'); $count=count($this->input->post('work')); <!-- Add This --> $data = $new_data = array(); $old_ids = array(); for($i=0; $i<$count; $i++) { <!-- Add This --> if(!in_array($form_id[$i],$old_ids)){ $old_ids[] = $form_id[$i]; $data[$i] = array( 'name' =>$this->session->userdata('emp_name'), 'date' =>$todayDate, 'work' =>$work[$i], 'partner' =>$partner[$i], 'director' =>$director[$i], 'time' =>$time[$i], 'task' =>$task[$i], 'status' =>$status[$i], 'form_id' =>$form_id[$i], 'updated_at' =>$updatedDate, ); }else{ $new_data[$i] = array( 'name' =>$this->session->userdata('emp_name'), 'date' =>$todayDate, 'work' =>$work[$i], 'partner' =>$partner[$i], 'director' =>$director[$i], 'time' =>$time[$i], 'task' =>$task[$i], 'status' =>$status[$i], ); } } <!-- Add This --> $update=$this->update->update_hodm($data,'form_id'); $add = $this->update->addnew_hodm($new_data); /* Display Success message if data updated successfully in database*/ if($update && $add){ <!-- Add This --> $this->session->set_flashdata('hodm_form',"All HODM Data Updated and Inserted Successfully."); $this->session->set_flashdata('hodm_form_class','alert-success'); }else if($update){ <!-- Add This --> $this->session->set_flashdata('hodm_form',"All HODM Data Updated Successfully."); $this->session->set_flashdata('hodm_form_class','alert-success'); } else if($add){ $this->session->set_flashdata('hodm_form',"All HODM Data Inserted Successfully."); $this->session->set_flashdata('hodm_form_class','alert-success'); }else{ /* Displaying the error message*/ $this->session->set_flashdata('hodm_form',"failed to add, Please Try again"); $this->session->set_flashdata('hodm_form_class','alert-danger'); } return redirect('home'); } else { $this->load->view('public/digital_hodm_view'); } } Model : <!-- Add This --> public function addnew_hodm($data){ if(empty($data)){ return false; } $this->db->insert_batch('task_form', $data); return ($this->db->affected_rows()>0?TRUE:FALSE); } <!-- Add This --> public function update_hodm($data,$where){ if(empty($data)){ return false; } $this->db->update_batch('task_form', $data,$where); return ($this->db->affected_rows()?TRUE:FALSE); }

Sultan · Accepted Answer · 2016-08-03 12:07:22Z

If there is unique/primary key in that table, add the key for each data. Assuming the key is task_id, update the code in your controller:

 ... ... ... 'task' =>$task[$i], 'status' =>$status[$i] ); if(!empty($task_id[$i]))$data[$i]['task_id'] = $task_id[$i]; //Add task ids for old data } $add=$this->update->update_hodm($data,$todayDate);

In your model, update the function with following code:

foreach($data as $array) { $query = 'INSERT INTO `table` SET '; $sep = $setvalues = ''; foreach($array as $key=>$value) { $setvalues .= $sep.$key.' = "'.$value.'"'; $sep = ','; } $query .= $setvalues.' ON DUPLICATE KEY UPDATE '.$setvalues.';'; $this->db->query($query); }

Aman Maurya · Accepted Answer · 2016-08-03 12:31:22Z

VIEW :

<?php $attributes = array( 'class' => 'form-horizontal', 'id' => 'update_form' ); echo form_open('emp_hodm_update/update_hodm', $attributes); ?> <table class="table table-striped table-hover table-responsive"> <thead> <tr class=""> <th>date</th> <th>Work</th> <th>Partner</th> <th>Director</th> <th>Time</th> <th>Task</th> <th>Status</th> <th>Action</th> </tr> </thead> <tbody> <?php foreach($result as $row) { ?> <tr> <td> <input class="form-control" type="hidden" name="form_id[]" value="<?php echo $row->form_id; ?>" class="" /> <input class="form-control" type="text" name="date[]" value="<?php echo $row->date; ?>" class="" /> </td> <td> <input class="form-control" type="text" name="work[]" value="<?php echo $row->work; ?>" class="" /> </td> <td> <input class="form-control" type="text" name="partner[]" value="<?php echo $row->partner; ?>" class="" /> </td> <td> <input class="form-control" type="text" name="director[]" value="<?php echo $row->director; ?>" class="" /> </td> <td> <input class="form-control" type="text" name="time[]" value="<?php echo $row->time; ?>" class="" /> </td> <td> <textarea class="form-control" name="task[]" rows="2" id=""><?php echo $row->task; ?></textarea> </td> <td> <textarea class="form-control" name="status[]" rows="2" id=""><?php echo $row->status; ?></textarea> </td> <td> <a href="" class="btn btn-danger">Delete</a> </td> </tr> <!-- start :if extra row is add on the button click --> <tr> <td> <input class="form-control" type="text" name="date[]" value="" class="" /> <!-- make sure at time of addition value of form_id[] should be empty --> <input class="form-control" type="hidden" name="form_id[]" value="" class="" /> </td> <td> <input class="form-control" type="text" name="work[]" value="" class="" /> </td> <td> <input class="form-control" type="text" name="partner[]" value="" class="" /> </td> <td> <input class="form-control" type="text" name="director[]" value="" class="" /> </td> <td> <input class="form-control" type="text" name="time[]" value="" class="" /> </td> <td> <textarea class="form-control" name="task[]" rows="2" id=""></textarea> </td> <td> <textarea class="form-control" name="status[]" rows="2" id=""></textarea> </td> <td> <a href="" class="btn btn-danger">Delete</a> </td> </tr> <!-- end : --> <?php } ?> </tbody> </table> <input class="btn btn-primary" type="submit" name="submit" value="submit"> <input class="btn btn-primary" type="submit" name="submit" value="add"> <?php echo form_close(); ?>

Controller:

public function update_hodm(){ /* Checking the all validation of task form*/ // $this->form_validation->set_rules('date', 'Date', 'required'); $this->form_validation->set_rules('work[]', 'Types of Work', 'required'); // $this->form_validation->set_rules('partner[]', 'Worked With', 'required'); $this->form_validation->set_rules('director[]', 'Director', 'required'); $this->form_validation->set_rules('time[]', 'No Of Hours', 'required'); $this->form_validation->set_rules('task[]', 'Task Details', 'required'); $this->form_validation->set_rules('status[]', 'Task Status', 'required'); if ($this->form_validation->run()) { foreach($_POST['form_id'] as $key => $value) { $data = array( 'name' => 'aman', 'date' => $_POST['date'][$key], 'work' => $_POST['work'][$key], 'partner' => $_POST['partner'][$key], 'director' => $_POST['director'][$key], 'time' => $_POST['time'][$key], 'task' => $_POST['task'][$key], 'status' => $_POST['status'][$key] ); if (!empty($value)) { // update $this->update->update_hodm($data, $value); } else { // insert $this->update->insert_hodm($data); } } } else { $this->load->view('public/digital_hodm_view'); } }

Model:

public function update_hodm($data,$form_id){ $this->db->where('form_id',$form_id); $this->db->update_batch('task_form', $data,'date');//date is my table column name return true; } public function insert_hodm($data){ $this->db->insert('task_form', $data);name return true; }

I'm not exactly sure that this will exactly work which you want , because most of the thing I have assume and then code, but this is almost what you wanted as I could understand from you question,hope it will work for you.Tell me if an extra help need

Collectives™ on Stack Overflow

Insert and update in one query in mysql codeigniter

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related