Need query to move one group to another

Hello MYSQL Guru's
Is there a query I can use to move everyone that is in group 3 to group 5

In the threads admin area, there is a "Mass Group Change" option... wouldn't that do what you need?

That might work for a small group, but I need to move about 14,000 users.

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.

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.

The script I posted in this thread might be helpful to do that.

https://www.ubbdev.com/forum/showflat...;o=&fpart=1

Umm, I think that thread got me more confused.

Code

<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.php
This 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:

Code

<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?

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)

Code

<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.

What about doing this with SQL

UPDATE w3t_Users SET U_Groups = REPLACE(U_Groups, '-3-', '-5-');

Josh, dimopoulos
Is there any way to test these scripts before I use them?

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.

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.

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.

Code

<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.

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.

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.

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.

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-');

[]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

This query worked as well:

Code

<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.

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.