How to make insert if id not exist and update if exist?

Question

I have a form and I'm using a codeigniter,Now I want to do update/insert. Because All I can do now is only update. Example:

If my project_id 51 exist it will update but if not exist it will insert.

Here's database layout

tbldevdetails

dev_id | project_id | dev_devloper | dev_purchase_date | dev_handover_date |dev_oqood_status | dev_contract | dev_email | dev_landline | dev_mobile

View

<!-- DEVELOPER DETAILS MODAL-->
<div class="modal fade" id="add-edit-dev" tabindex="-1" role="dialog">
   <div class="modal-dialog">
      <?php echo form_open(admin_url('projects/add_edit_devdetails/'.$project->id)); ?>
      <div class="modal-content">
         <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            <h4 class="modal-title"><?php echo _l('dev_title'); ?></h4>
         </div>
         <div class="modal-body">
            <div class="row">
                    <div class="col-md-6" id="tc_cperiod_to">
                        <?php $value = (isset($project) ? _d($project->dev_purchase_date) : _d(date('Y-m-d'))); ?>
                        <?php echo render_date_input('dev_purchase_date','dev_purchase_date',$value); ?>
                    </div>

                  <div class="col-md-6" id="dev_handover_date">
                        <?php $value = (isset($project) ? _d($project->dev_handover_date) : _d(date('Y-m-d'))); ?>
                        <?php echo render_date_input('dev_handover_date','dev_handover_date',$value); ?>
                    </div>
                </div>


            <div class="row">
                    <div class="col-md-6">
                     <?php echo render_input('dev_oqood_status','dev_oqood_status',isset($project) ? $project->dev_oqood_status : '','text'); ?>
                    </div>

                    <div class="col-md-6">
                     <?php echo render_input('dev_contact','dev_contact',isset($project) ? $project->dev_contact : '','text'); ?>
                    </div>
            </div>

            <div class="row">
                    <div class="col-md-4">
                     <?php echo render_input('dev_email','dev_email',isset($project) ? $project->dev_email : '','text'); ?>
                    </div>

                    <div class="col-md-4">
                     <?php echo render_input('dev_landline','dev_landline',isset($project) ? $project->dev_landline : '','text'); ?>
                    </div>

                    <div class="col-md-4">
                     <?php echo render_input('dev_mobile','dev_mobile',isset($project) ? $project->dev_mobile : '','text'); ?>
                    </div>
            </div>
        </div>
        <div class="modal-footer">
         <button type="button" class="btn btn-default" data-dismiss="modal"><?php echo _l('close'); ?></button>
         <button type="submit" class="btn btn-info" autocomplete="off" data-loading-text="<?php echo _l('wait_text'); ?>"><?php echo _l('submit'); ?></button>
      </div>
   </div>
   <!-- /.modal-content -->
   <?php echo form_close(); ?>
</div>
<!-- /.modal-dialog -->
</div>
<!-- /.modal -->

Controller

 public function add_edit_devdetails($project_id)
    {
        if (has_permission('projects', '', 'edit') || has_permission('projects', '', 'create')) {
            $this->developer_model->update($this->input->post(), $project_id);
            redirect($_SERVER['HTTP_REFERER']);
        }
    }

Model

public function update($data, $id)
    {


        $_data['data'] = $data;
        $_data['project_id']   = $id;

        $_data = do_action('before_update_project', $_data);
         $data = $_data['data'];

        $this->db->where('project_id',$id);
        $pid = $this->db->get('tbldevdetails');


        if($pid->num_rows() < 0)
        {

            $this->db->insert('tbldevdetails', $data);
        } else {
            $this->db->where('project_id', $id);
            $this->db->update('tbldevdetails', $data);
        }
    }
}

My update is working on this code but my insert is not working.

Thankyou

Try this statement - if($pid->num_rows() == 0) instead if($pid->num_rows() < 0) in your model — Sumit, Feb 27 '18 at 07:22
possibly answered here already.https://stackoverflow.com/questions/29318749/codeigniter-active-record-insert-if-new-or-update-on-duplicate — gautamaggarwal, Feb 27 '18 at 07:23
@Sumit it works ! but it becomes vice versa. my update is not working — Carlvic Lim, Feb 27 '18 at 07:29
@gautamaggarwal Yes I use that as a guide but I have problem in different ways — Carlvic Lim, Feb 27 '18 at 07:30
Try this statement print_r($pid); die(); in model page after $pid = $this->db->get('tbldevdetails'); Show your output.I think you don't receive any data from database.So you always get 0 number of rows. — Sumit, Feb 27 '18 at 07:42
Hi guys , Thanks for comments and suggestion I solve my problem I just change my statement into this $this->db->where('project_id',$id); $pid = $this->db->get('tbldevdetails')->row(); if($pid == 0 ) Thanks! — Carlvic Lim, Feb 27 '18 at 09:10

score 0 · Answer 1 · answered Feb 27 '18 at 07:36

0

Please follow the link where you can insert update from a single query.If row is already exist then automatically update the row, which is handle in mysql server.

answered Feb 27 '18 at 07:36

Sumit

1,702
2
14
20

score 0 · Answer 2 · answered Feb 27 '18 at 19:07

First, make sure the argument to add_edit_devdetails() is set appropriately in the view. In this approach we don't add $project->id to the action url if it was not passed (not set) by the controller.

<?php 
$action = 'projects/add_edit_devdetails';
//append the id if provided
if(isset($project->id))
{
    $action .= "/{$project->id}";
}
echo form_open($action); 
?>

In the form's action method we use a default argument so that when a argument value is not supplied the default is used.

public function add_edit_devdetails($project_id = NULL)
{
    if(has_permission('projects', '', 'edit') || has_permission('projects', '', 'create'))
    {
        $this->developer_model->update($this->input->post(), $project_id);
        redirect($_SERVER['HTTP_REFERER']);
    }
}

In the model we simply check that $id is set. If it is, update else insert.

public function update($data, $id)
{
    if(isset($id))  //FALSE if $id===NULL
    {
        $this->db->update('tbldevdetails', $data, array('id' => $id));
    }
    else
    {
        $this->db->insert('tbldevdetails', $data);
    }
}

I left out the call to do_action('before_update_project', $_data); because it seems to be related to making a database call to determine if the value of $id exists in the database already. I am assuming that value is an auto increment field and the controller that opens the view knows whether this is an exiting record - or not.

How to make insert if id not exist and update if exist?

2 Answers2