|
|
Joined: Sep 2001
Posts: 64
Junior Member
|
|
Junior Member
Joined: Sep 2001
Posts: 64 |
I've got a generic group that all of my users must be a part of. However, I have a chunk of users that aren't for some reason or another. So, I did a query to see who they are <br />SELECT * <br />FROM `w3t_Users` <br />WHERE U_Groups NOT <br />LIKE "%-3-%" Now I need to do a query to manually enter -3- for each one that doesn't have that group. I would use "Update w3t_Users; set U_Groups="-3-"; but that would overwright the groups that are already there. So what is the SQL statment that would ADD -3- without damaging what is already there?
|
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
|
Member
Joined: Apr 2001
Posts: 3,266 |
I could help you if I follow.
In Threads the default group for Admins is 1 Mods 2 and User's 3
Why would you need to add -3- when they should already be -3-??
This should work for one user
UPDATE w3t_Users SET U_Groups="ALLGROUPS IN THIS FORMAT -1-2-3-" WHERE U_Username="USERNAME"
If you give me some more insight I may be able to help further.
|
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
I ran across this this weekend. I couldn't figure out how to do that with a query - as you have to add "3-" to the end of whatever groups are there.  I ended up writing a little script to do this. Let me dig it up for you. 
|
|
|
|
|
Joined: Feb 2002
Posts: 2,286
Veteran
|
|
Veteran
Joined: Feb 2002
Posts: 2,286 |
Can't the mass group change script be used in admin for this to join all those that don't belong to the group to it?
Fans Focus - Focusing on Fans of Sport (Okay - mainly football (the British variety  at the moment - but expanding all the time....)
|
|
|
|
|
Joined: Sep 2001
Posts: 64
Junior Member
|
|
Junior Member
Joined: Sep 2001
Posts: 64 |
Well - to answer Omegatron - I run a very restricted board. You've got to register first (which puts you in group -6-) then upon approval, the admin has to manually put the user in the groups that they should have access to. Admins and Mods arent' a problem as they're already set up. Since the board is restricted, it doesn't automatically put people in -3-, and therein lies the problem. I had a little learning curve with the other admins and some folk got lost in the shuffle. There's less than 100 folks that are affected by this, but I simply do not have the time to go change that many records manually.
Josh - lemme know when you find that script. That would be most helpful.
|
|
|
|
|
Joined: Sep 2001
Posts: 64
Junior Member
|
|
Junior Member
Joined: Sep 2001
Posts: 64 |
Unfortunately, Ian, the mass group change can manipulate users that are already in a group, but cannot ADD users that aren't in it.
|
|
|
|
|
Joined: Feb 2002
Posts: 2,286
Veteran
|
|
Veteran
Joined: Feb 2002
Posts: 2,286 |
I thought it could - my mistake then. Sorry.
Fans Focus - Focusing on Fans of Sport (Okay - mainly football (the British variety at the moment - but expanding all the time....)
|
|
|
|
|
Joined: Sep 2001
Posts: 64
Junior Member
|
|
Junior Member
Joined: Sep 2001
Posts: 64 |
|
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
|
Member
Joined: Apr 2001
Posts: 3,266 |
Ah OKay I understand now. You have a highly modified script  I kept banging my head against the wall trying to figure why user's where not in group 3 when thats the default in the Threads scripts.
|
|
|
|
|
Joined: Mar 2003
Posts: 215
Junior Member
|
|
Junior Member
Joined: Mar 2003
Posts: 215 |
If you do:
SELECT U_Username, U_Groups FROM 'w3t_Users'
WHERE U_Groups NOT LIKE "%-3-%"
ORDER BY U_Groups
If the U_Groups values you want to change are all the same, or there is only a few variations:
UPDATE 'w3t_Users'
SET U_Groups = "NEWGROUPVALUES"
WHERE U_Groups = "OLDGROUPVALUESTOREPLACE"
Something like this for example if most of the users are only "-6-":
UPDATE 'w3t_Users'
SET U_Groups = "-3-6-"
WHERE U_Groups = "-6-"
Or do I not follow what you want to do?
|
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
I think he wants to add group 3 to whatever groups they already have.
|
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Untested..... backup your database first: But this should work if you save it like a script and place it in your threads directory and run it in your browser: <br /><?<br /><br />require("main.inc.php");<br />$i = 0;<br /> $query = "<br /> SELECT U_Groups, U_Number, U_Username<br /> FROM {$config['tbprefix']}Users<br /> WHERE U_Groups NOT LIKE '%-3-%'<br /> ";<br /> $sth = $dbh -> do_query($query);<br /> while(list($groups,$number,$username) = $dbh -> fetch_array($sth)) {<br /> $newgroups = $groups."3-";<br /> $newgroups_q = addslashes($newgroups);<br /> $query = "<br /> UPDATE {$config['tbprefix']}Users<br /> SET U_Groups = '$newgroups_q'<br /> WHERE U_Number = $number<br /> ";<br /> $stj = $dbh -> do_query($query);<br /> echo "$username was $groups and was reset to $newgroups <br />";<br /> $i++;<br /> }<br /> <br /> echo "Done - updated $i users.<br />";<br /> <br /> ?><br />
|
|
|
|
|
Joined: Mar 2003
Posts: 215
Junior Member
|
|
Junior Member
Joined: Mar 2003
Posts: 215 |
(Backup db first)
UPDATE w3t_Users
SET U_Groups = CONCAT(U_Groups,"3-")
WHERE U_Groups NOT LIKE '%-3-%'
And to see what this will replace before by doing:
SELECT count(*), U_Groups, CONCAT(U_Groups,"3-") FROM w3t_Users
WHERE U_Groups NOT LIKE '%-3-%'
GROUP BY U_Groups
Last edited by RandyJG; 08/20/2003 7:11 PM.
|
|
|
|
|
Joined: Sep 2001
Posts: 64
Junior Member
|
|
Junior Member
Joined: Sep 2001
Posts: 64 |
thanks everyone - I'll work on trying these out.
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
|
Posts: 69
Joined: January 2001
|
|
|
Forums63
Topics37,575
Posts293,931
Members13,824
| |
Most Online6,139
Sep 21st, 2024
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|
