1
liomj
user and group maintenance help
  • 2021/4/22 8:29

  • liomj

  • Just popping in

  • Posts: 77

  • Since: 2012/4/10


Hi i need help to do maintenance on my xoops user through mysql query

let say I have an office intranet xoops site user database > 1000

if the user resign user will be set to inactive and put into a special group inactive user (let say groupid 5)

i have more than 10 groups,
how can i set mysql query to delete all the groups_users_link for the inactive user and set them to belong to only group id number 5 (inactive user)

2
alain01
Re: user and group maintenance help
  • 2021/4/22 10:46

  • alain01

  • Just can't stay away

  • Posts: 528

  • Since: 2003/6/20


Interresting,

I 've tryed but my MYSQL courses are too far in my head...

I understand what we should do it but not in SQL !

1 - find all uid with with groupid = 5 (easy : SELECT * FROM `xe07_groups_users_link` WHERE `groupid` = 5)
2 - List for each uid with groupid=5, and find their other groupid <> 5
3 - delete these lines

So you keep all uid with groupid = 5

I want to see the SQL request

"remove duplicate occurrence in SQL" but dont keep the first occurrence (keep the occurence with groupid = 5 )

3
geekwright
Re: user and group maintenance help

The table you want is groups_users_link

I would suggest
DELETE FROM groups_users_link WHERE uid uid-to-remove


followed by
INSERT INTO groups_users_link(groupiduidVALUES ([special-group-id],[uid-to-remove])

4
goffy
Re: user and group maintenance help
  • 2021/4/22 18:06

  • goffy

  • Just can't stay away

  • Posts: 535

  • Since: 2010/12/27


hi

i would suggest:
first determine all relevant uids/links from group 5 and store them in a temporary table
Quote:
CREATE TABLE temp as SELECT linkid, groupid FROM xe07_groups_users_link as query1 INNER JOIN (SELECTxe07_groups_users_link.uid FROM xe07_groups_users_link WHERE (((xe07_groups_users_link.groupid)=5))) AS query2 ON query1.uid = query2.uid;


then delete all items from table groups_users_link from this uids where group_id <> 5
Quote:
DELETE xe07_groups_users_link,temp FROM xe07_groups_users_link INNER JOIN temp ON temp.linkid = xe07_groups_users_link.linkid WHERE xe07_groups_users_link.groupid <> 5


please make data backup before

5
liomj
Re: user and group maintenance help
  • 2021/4/23 3:18

  • liomj

  • Just popping in

  • Posts: 77

  • Since: 2012/4/10


Quote:

goffy wrote:
hi

i would suggest:
first determine all relevant uids/links from group 5 and store them in a temporary table
Quote:
CREATE TABLE temp as SELECT linkid, groupid FROM xe07_groups_users_link as query1 INNER JOIN (SELECTxe07_groups_users_link.uid FROM xe07_groups_users_link WHERE (((xe07_groups_users_link.groupid)=5))) AS query2 ON query1.uid = query2.uid;


then delete all items from table groups_users_link from this uids where group_id <> 5
Quote:
DELETE xe07_groups_users_link,temp FROM xe07_groups_users_link INNER JOIN temp ON temp.linkid = xe07_groups_users_link.linkid WHERE xe07_groups_users_link.groupid <> 5


please make data backup before


thank you all

im using geekwright method for now

i need to use php to deal with array
DELETE FROM `xxxx_groups_users_linkWHERE `uidin (SELECT `uidfrom `xxxx_userswhere uid in (396,12))
DELETE FROM `xxxx_groups_users_linkWHERE `uidin (SELECT `uidfrom `xxxx_userswhere uid='396')
INSERT INTO `xxxx_groups_users_link`(groupiduidVALUES (5,396)

Login

Who's Online

226 user(s) are online (162 user(s) are browsing Support Forums)


Members: 0


Guests: 226


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits