|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
Hello MYSQL Guru's
Is there a query I can use to move everyone that is in group 3 to group 5
|
|
|
|
|
Joined: Feb 2002
Posts: 1,759
Addict
|
|
Addict
Joined: Feb 2002
Posts: 1,759 |
In the threads admin area, there is a "Mass Group Change" option... wouldn't that do what you need?
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
That might work for a small group, but I need to move about 14,000 users. 
|
|
|
|
|
Joined: Feb 2002
Posts: 1,759
Addict
|
|
Addict
Joined: Feb 2002
Posts: 1,759 |
Hmm, I just looked at /admin/dochangeugroup.php and it is a really simple script with just a couple MySQL queries. I don't see that being much different than typing the queries in manually.
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
Well, when I get to the screen where I can "move" groups from one to the other, I can see the list (group) where I want to move the others in, the problem is that in the other window is a list of "all" groups (I verified), I don't want to move all groups just certain ones and there is no way of knowing which users belong in wich group before I move them, does that make sence? I used the "Or choose a Group to search for:" from the previous screen but it does not work correctly at all, I think that part is a bug that has been there forever, anyway, surly typing in a comand would be much much faster and safer than hopeing I get it right with the mass move thing that I just don't trust.
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
Umm, I think that thread got me more confused.
|
|
|
|
|
Joined: Jun 2003
Posts: 1,025
Junior Member
|
|
Junior Member
Joined: Jun 2003
Posts: 1,025 |
<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 LIKE '%-3-%'<br /> ";<br /> $sth = $dbh -> do_query($query);<br /> while(list($groups,$number,$username) = $dbh -> fetch_array($sth)) {<br /> $newgroups = $groups."3-";<br /> $query = "<br /> UPDATE {$config['tbprefix']}Users<br /> SET U_Groups = '%-5-%'<br /> WHERE U_Number = $number<br /> ";<br /> $stj = $dbh -> do_query($query);<br /> echo "$username was $groups and was reset to group 5 <br />";<br /> $i++;<br /> }<br /> <br /> echo "Done - updated $i users.<br />";<br /> <br /> ?><br />Open notepad, paste the above into notepad, save as updategroups.php(or whatever you choose.php), make sure the file type is set to all *.*, then upload it to your threads directory. Close your forums and backup your database. Then in your browser enter: www.yourdomain.com/yourthreadsdirectory/updategroups.phpThis will run the script that should update everyone in group 3 to group 5. This seems bloated for what you want to do. I am not familer enough with MySQL to tell you that this MySQL command is all you need: <br />SELECT U_Groups<br />FROM {$config['tbprefix']}Users<br />WHERE U_Groups LIKE '%-3-%'<br />UPDATE {$config['tbprefix']}Users<br />SET U_Groups = '%-5-%'<br />But from what I gleamed from Josh's code, that seems like that is MySQL command set you would need and it would be leaner for your purpuses. Since, as I understand it you simply want to change users from group 3 to group 5, leaving group 3 empty. Or am I way off on this?
|
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
You can't really use a direct MySQL Command (that I'm aware of) if you want to update groups - because you need to add the group number to the end of whatever existing groups they have. So if you did the query - you could take everyone that had 3 in it and change it to '-5-' (I think the % would break the SET line) then they just wouldn't have any other custom groups that they had before. I think maybe something like this would change anyone in group 3 to group 5, without disrupting any of their other groups (like admin, mod etc....). (So they would be REMOVED from 3 and ADDED to 5) <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 LIKE '%-3-%'<br /> ";<br /> $sth = $dbh -> do_query($query);<br /> while(list($groups,$number,$username) = $dbh -> fetch_array($sth)) {<br /> $newgroups = str_replace("-3-","-5-",$groups);<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 /> ?>I *think* that'll do it. Backup your database first. 
|
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
What about doing this with SQL
UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-3-', '-5-');
Nikos
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
Josh, dimopoulos
Is there any way to test these scripts before I use them?
|
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
What you can do is do it on a test board you might set up. Alternatively you can export the w3t_Users table using phpMyAdmin and then put the file in a new database. Run the replace SQL command and then check the table if the groups have been updated. Once you are happy run it against your main users table.
Nikos
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
Anyone out there have a small test board they can run this for me?  Josh, no one in group 3 is a Mod or Admin. slayer60 "Since, as I understand it you simply want to change users from group 3 to group 5, leaving group 3 empty. Or am I way off on this?" That is exactly right.
|
|
|
|
|
Joined: Jun 2003
Posts: 1,025
Junior Member
|
|
Junior Member
Joined: Jun 2003
Posts: 1,025 |
Looking at my query above it seems like it would set all users to group 5, instead of just those in group 3. I think this should do it. <br />SELECT U_Groups<br />FROM w3t_Users<br />WHERE U_Groups LIKE '%-3-%'<br />SET U_Groups = '%-5-%'<br /> Its untested. I'll set up a test database and see if it does the trick.
|
|
|
|
|
Joined: Jun 2003
Posts: 1,025
Junior Member
|
|
Junior Member
Joined: Jun 2003
Posts: 1,025 |
I couldn't get my SQL querry to work. I couldn't figure out the syntax.
I tested Josh's .phpfile. That successfully changed my users from group 3 to group 5. So all you need to do is save Josn's script as a .php file, upload it to your threads directory and run it. Make suer you remove it from your server when you are done.
Close your boards and backup your database before runing it though.
|
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Slayers should work too.... except you don't want the % in the set command. So the line would be SET U_Groups = '-5-' Although I think Nikos was onto the more advanced query that I didn't know.
|
|
|
|
|
Joined: Jun 2003
Posts: 1,025
Junior Member
|
|
Junior Member
Joined: Jun 2003
Posts: 1,025 |
Even with the line:
SET U_Groups = '-5-'
It still gives syntax errors:
[qoute] MySQL said:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET U_Groups = '-5-'' at line 5[/qoute]
I'm not sure why it is giving that error. Maybe I will understand after I've had some sleep.
|
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
Colorado and Slayer,
I just tested the
UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-3-', '-5-');
and it works fine. Also you might want to run the following as well to avoid two occurances of the same group in the field
UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-5-5-', '-5-');
Nikos
|
|
|
|
|
Joined: Jul 2001
Posts: 442
Enthusiast
|
|
Enthusiast
Joined: Jul 2001
Posts: 442 |
[] dimopoulos said:Colorado and Slayer, I just tested the UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-3-', '-5-'); and it works fine. Also you might want to run the following as well to avoid two occurances of the same group in the field UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-5-5-', '-5-'); [/] Worked like a charm, thank you so much  
|
|
|
|
|
Joined: Jun 2003
Posts: 1,025
Junior Member
|
|
Junior Member
Joined: Jun 2003
Posts: 1,025 |
This query worked as well: <br />UPDATE devbo_Users<br />SET U_Groups = '-5-'<br />WHERE U_Groups LIKE '%-3-%';<br /> If the user was in 3 and 5 it is now just in 5. Nikos's query looks pretty cool, and seems to work fine, but I wanted to get mine working. SELECT * FROM * SET * WHERE * doesn't seem to be valid sql logic. I guess it is a cross of logic strings. I actually pilfered this command structure from a post about replacing icon names.
|
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
You re right Slayer. The logic is SELECT * FROM * WHERE * ORDER BY *. The SET comes only on an UPDATE * SET * = ... where ... can be a SELECT query.
Nikos
|
|
|
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: 1,157
Joined: July 2001
|
|
|
Forums63
Topics37,573
Posts293,925
Members13,849
| |
Most Online5,166
Sep 15th, 2019
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|
