phpmysqlmysqlipicklist

MYSQL Consolidate Conditional Update from Picklist Array

I am struggling to find a good way to approach this code with MYSQL and PHP. I have a table of users and a table of groups. Each user will only belong to one group, or will belong to group 0, (not in a group).

I have an administration edit group page, which has the JS Picklist Plugin in it. It lists the users which are in the group, and those that are not. On submission of the form on this page, I get an array of users which are the only ones which should be in the group after the sql query.

So I need to do two things:

  1. Remove the group id from any users which currently are in the group, but are not in the array returned by the form.

  2. Ensure any users in the array returned have the group id updated to them.

For this example, I have a group ID of 25, after the form has been submitted there are two be two users in this group (44 and 45). No other users should remain in that group. But if the other users are in a different group such as 30, they should not be modified either. I could have several hundred users, and whilst performance isn't the greatest concern, it just feels a bit hackish to me.

This is what I've failed at so far. For performance reasons we are talking maybe 300 users, so nothing to massive. The two queries do work as expected, just seems very hackish. Must be a better way, here to learn!

update users set user_group = 0 where (user_group = 25 AND uid != 45) AND (user_group = 25 AND uid != 44)

update users set user_group = 25 where ( uid = 44 OR uid = 45 )

Thanks!

Solution 
  • UPDATE users 
SET user_group = CASE 
  WHEN uid IN (...ids from post) THEN $group_id
  ELSE 0
 END
WHERE user_group = $group_id OR uid IN (...ids from post)

    Untested and posted from my phone but the concept is there