使用codeigniter删除并在数据库中插入多个记录

Question

I am working on one project and cannot figure out the solution of the current problem.

View

Admin can assign multiple or single role to user using checkbox as shown in following figure:

Now, when we hit the submit button. Here is what the controller does:

Controller

        $addRoles['userID']=$uid;
        $addRoles['addDate']=time();

        foreach($this->input->post('roles') as $val)
            {

            $addRoles['roleID']=$val;

            $this->admins->updateRole($addRoles);

            }

it is successfully storing all the data. Inside loop i call the model function to add data into database.

Now, what i want to do is that the all the existing roles must be deleted from database if current user has any because it will not allow duplication of values. Later, add all roles one by one in to the database. Here is my model code:

Model

    function updateRole($role){


    $this->db->where('userID', $role['userID']);
    $this->db->where('roleID', $role['roleID']);

    $this->db->delete('user_roles');
    $this->db->insert('user_roles', $role);
    return ($this->db->affected_rows() > 0) ? TRUE : FALSE;
}

Now, the problem I am experiencing is that when model runs the delete query it only deletes the record if that checked role is already in the database. Or if i delete only by user id then it will delete all the records every time query will run. I cannot simply insert records because i have to update the roles and roles can be assigned different at different times.

Any help will be appreciated. Thanks

Answer 1

You can do like by making two functions 1) saveRoles($role, $user_id) 2.)updateRoles($role, $user_id)

function saveRoles($role, $user_id)
{
      // can save roles of user using insert 

}

function updateRoles($role, $user_id)
{
     // delete all existing roles of that user 
     // with only user_id in where clause
     // after that you can call 
     $this->saveRoles($role, $user_id);
}

The updateRoles function will delete all existing entries and insert new set of values in Db that you want to update.

I hope this help you!