UBB.Developers Forums Community Development How-To/Site Help Library Useful MySQL Queries for UBB.Threads

How would I find out what languages my members are using?
so that when I upgrade I know what ones I can dump.

SELECT DISTINCT U_Language FROM w3t_Users

Thanks

Ok, I have now decided to turn OFF display names, but some users still have display names in the DB...

What would be the query to change everyone's display name to their login names? (So they are both the same)

?
UPDATE w3t_Users
SET U_Username = U_LoginName

Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the literal display name of "U_LoginName" so I wouldn't really recommend trying it.

I need a Query to reset everyones name color to the default "Normal" How would this be done?

well what is normal ? heh BUT you can try

UPDATE w3t_Users SET U_Color = ''

If you want to make the color of the names the default color you have to type "Normal" into the color section. It will make them the forum default.

So my guess is to do...
UPDATE w3t_Users SET U_Color = 'normal'
(could be worng though. dont run that unless you ask someone)

has to be normal? thought it could be empty? they change it? if it has to be normal then yes DrChaos is chaotically correct

I said I could be wrong

I canged a few and just removed the name of the color where edit the user and nothing happend.

then one of my admins said to try to put "normal", because that what all the regular users are set to, and it worked.

well then you are right now wrong and I am wrong not right so glad to meet ya

Can anyone tell me the query to require all users to review the Forum Rules and accept or reject on their next visit to the board?

It's that time

Using the Force Rules mod?

UPDATE w3t_Users
SET U_Agree = 0

Thanks Josh,I didnt know there was a Mod for that.

Anyone get brave enough to try this out?

[] ?
UPDATE w3t_Users
SET U_Username = U_LoginName

Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the literal display name of "U_LoginName" so I wouldn't really recommend trying it. [/]

I have a bunch of users who I need to make their eamil / fake email and user name / display name all match. A SQL call like this would save me a lot of time

P-

>UPDATE w3t_Users
>SET U_Username = U_LoginName
>Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the
>literal display name of "U_LoginName" so I wouldn't really recommend trying it.

Test it with one name first.

UPDATE w3t_Users
SET U_Username = U_LoginName
WHERE U_Number= 124124

124124 = your user number

Good idea

I ran the follwing query through MyPHPAdmin and it worked...

UPDATE w3t_Users
SET `U_Username` = `U_LoginName`

Thanks Anno

P-

Here is the old query by RandyJG that JoshPet used in the Stylesheet Popularity mod. Since we have been able to store queries in the past couple of versions, the mod is no longer needed, but the query could still be helpful...

How do I access a list of all of my users e-mail address? Can this be done with a query?

SELECT U_RegEmail, U_Email, U_Fakeemail FROM w3t_Users;

Worked a treat, thank you

Select U_Email from w3t_Users where u_number > 1;

How about a script to show user ID's that have been created from the same IP address, even if you dont know the IP address?

[]JoshPet said:
Using the Force Rules mod?

UPDATE w3t_Users
SET U_Agree = 0 [/]

This one no longer seems to work on 6.5.. is there a new way of doing this?

Ta

What would the script be to reset/update all users to no avatar?

UPDATE w3t_Users SET U_Picture = ''

I am wondering if anyone could help out with a query to basically duplicate a group under another name? I have about 5000 members and we're looking for a simple way to plan one heck of a going away party for one of our members without his knowledge. It would seem that this could be easily done by creating a new group and adding all members in the "users" group to this new group.... and then removing our guest of honor from the new group so he wouldn't have permissions to see our "hidden" party planning forum.

Any ideas? I've played with doing this manually, a couple members at a time, but it is rediculously slow going.

version of threads would be helpful since...most versions can do it already at least 6.4 and 6.5 by just adding a new user group and then selecting everyone in user group and adding them to new group?

6.4.1

I've already tried to do this through the AP using the "mass group change" but if I try to do any more then 4 or 5 members at a time it just grinds to a halt.

I need a query to do the following:

List all usernames + email adresses + number of PM's in their inbox from users with more than 2 PM's in their INBOX

How?

Ok, I think I got it, but I'm not 100% if this is correct:


--------------------------------------------------------------------------------

SELECT w3t_Users.U_Email,
w3t_Users.U_LoginName,
w3t_Users.U_Username,
COUNT(w3t_Messages.M_Uid)
FROM w3t_Messages, w3t_Users
GROUP BY w3t_Users.U_Number
HAVING COUNT(w3t_Messages.M_uid) > 2

I tried to run this, but my browser seemed to hang. I cancelled the query after 20 minuten (my whole database is only 40mb).

What am I doing wrong here ?

To delete pm's (like all those welcome pm's that re-appear after upgrading), try these commands:


Change the topic subject wording to whatever you send to new registered members.

There's still the ubbt_PRIVATE_MESSAGE_USERS table - I imagine a script would need to be written to match the pm with the topic id being deleted above and clean that out too.

This is what I do:

I get a list of Users who have less than 50 posts and have done a rating on somebedy.

I also get the name of the user who rated but I don't know how to get the name of the user who WAS rated (R_What is user ID).

Can anyone provide me with solution?

Next question is how to update U_Rating, U_Rates, U_RealRating to match values in R_Rating?

Of course without having to write a script but rathrer running a query

What version are you running?

6.5.5

I'll write the script but I would like to learn how to do it via sql if possible

You are querying for R_What - that's not giving you the result you are looking for?

This really isn't quite a mod, but may help those wanting to increase activity on their sites. Currently it's a bit of a jump through the hoops to get emails for watched topics and forums - I've repeatedly had to tell people the couple of things they have to do to get emails.

Here's the mysql commands you put into your SQL Command window to set everyone to receive emails for replies to any topics they are watching:



and if you also want to set all users to receive email when they get a pm, use this one:


And lastly, if you want all users to receive emails from admins, then use this one:


note: the last one might get a few complaints since I believe it specifically is set by the user to opt out, I may be wrong - it's been a while since I was a newbie

In addition to those commands from Allen, I would strongly recommend editing all your emails that go out - at the moment they are very weak, and do not let the user know how to unsubscribe from the email that they have just received.

For example my notification of a PM says...


Nothing elaborate - but it gets around anyone accusing us of spamming them.

And all my emails have the subject starting...

FansFocus.Com: