19164

Delete and insert multiple records in database using codeigniter

Question:

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

<strong>View</strong>

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

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/S6w8U.jpg" data-original="https://i.stack.imgur.com/S6w8U.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

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

<strong>Controller</strong>

$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:

<strong>Model</strong>

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

Answer1:

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!

Recommend

  • Stored procedure to list all weeks between two dates
  • Translating results by day to results by week with excel vba
  • Get correct checkbox value when using razor asp.net mvc
  • Convert Future[List[String]] to List[String]
  • How to implement a relationship between IdentityRole (dbo.AspNetRoles) and a custom entity?
  • How to make SQL Query to get the sum of specific column?
  • Collapsible Sankey Diagram - D3
  • Attemping to change attributes of $(this) inside success function
  • WooCommerce hook after order is updated?
  • Number of threads being used during Parallel.ForEach
  • Updating and removing unique join relationships in CakePHP
  • Can't access Tomcat 7 Manager app when running from Eclipse
  • Is there a way to disable a specific event in kendo ui scheduler?
  • Serve file to user over http via php
  • Efficient algorithm to find additions and removals from 2 collections
  • Is it better to have roles as a column on my users table, or do it through join tables (Roles &
  • Enumerating Controls on a Form
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • XSLT foreach repeating nodes to flat
  • Group list of tuples by item
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • List images(01.png) and descriptions(01.txt) from directory
  • rspec simple example getting error on request variable in integration test
  • D3 nodes and links from JSON with nested arrays of children
  • jQuery .attr() and value
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • what is the difference between the asp.net mvc application and asp.net web application
  • Matrix multiplication with MKL
  • Free memory of cv::Mat loaded using FileStorage API
  • Memory offsets in inline assembly
  • Turn off referential integrity in Derby? is it possible?
  • Authorize attributes not working in MVC 4
  • apache spark aggregate function using min value
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • EntityFramework adding new object to nested object collection
  • Binding checkboxes to object values in AngularJs
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize