xr33dx xr33dx - 3 months ago 15
HTML Question

Insert and update in one query in mysql codeigniter

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.


  1. 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










  2. 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.

  3. 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;
}



  1. Please help me to find the solution.



Thanks in advance

Answer

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