List of Useful SQL's

[Twitchin Kitten]

OK folks, sometimes you need to get things out of your board and just don't know how. Running an SQL is very useful if you know the right one to use.

Below is a list of handy SQL's, what each one is for and I'll add to them as needed.

Feel free to post your SQL if you know one.

I will take yours, and put it in the 'list' so if you see your post disappear, i'm just tidying up!

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

Activate new members when registration email fails:

Run this query and then go to the users tab in the admin panel. You will see lots of new members or none. These are case sensitive and must be done exactly:

Quote:UPDATE wowbb_users SET user_activation_key=''

Need to activate only one SPECIFIC member? You will need the member's name that they registered with to do this.
To activate an Unacvtivated User Account:

Quote:UPDATE wowbb_users SET user_activation_key='' WHERE user_name='example'

change the wording in RED to the user's name that needs to be activated.

Allows you to see members with the same e-mail address on different accounts, and also to see if they have any posts:

Quote:SELECT user_name, user_email, user_posts, user_joined FROM wowbb_users ORDER BY user_email LIMIT 1000

Is there a sql I could run that will tell me how many people joined before a certain date and have never posted?

Quote:select * from wowbb_users where user_posts =0 and user_joined < 20060831 LIMIT 250

This SQL query will give you a list of all of your member's usernames and email addresses.

Quote:SELECT user_name, user_email FROM wowbb_users

Hate members signatures? Do you have them turned off on your board but someone keeps sneakig them in on you? Try this to take them out on everyone all at once:

Quote:UPDATE wowbb_users SET user_signature=''

Need to reset someone's password manually? Simply run this and then inform them via email of their new password. This is only if the 'forgot your password' link in the log in area does not work for them.

Quote:UPDATE wowbb_users SET user_password=md5('newpassword') WHERE user_name='username'

Insert a password for them where indicated in RED. insert user's name where indicated in RED.
Make sure you email the user with the new password and tell them to come change it!

Do you have a special theme you are using and you want people to use that one exclusively? Run this query before setting the users to all use the same theme.

Quote:UPDATE wowbb_users SET user_theme='theme name here'

Then go to your Admin panel> General and select the theme you want members to use.

To check on unactivated accounts.
All the unactivated members will be shown when you do this

Quote:SELECT user_name, user_email, user_joined FROM wowbb_users WHERE user_activation_key<>'' ORDER BY user_joined DESC[/php]

This will make all members browsing invisible visible again. This must be run BEFORE you comment out the ability to browse invisible in the theme templates. (see the tips and tricks section on commenting out invisible mode and more.)

[quote]UPDATE wowbb_users SET user_invisible=0

This will change only the specified member's theme and no one elses.

Quote:UPDATE wowbb_users SET user_theme='themename' WHERE user_name='username'

QUESTION:
Is it possible please to have an SQL query for viewing members with post counts between certain values, for instance between 50 and 100 posts?

I need this so I can move the more active ones to different groups (active member for instance) and currently I have to manually look through all the registered members to see who has reached the required post count.

GEORGEP ANSWER:

Quote:SELECT user_name, user_posts from wowbb_users WHERE user_posts BETWEEN 50 and 100

To move them to a new group, first find the new group number (i.e. 20) then issue:

Quote:UPDATE wowbb_users SET user_group_id=20 WHERE user_posts BETWEEN 50 and 100

No need to manually move people around if you have a set criteria as to the range of posts. Just save a bunch of these using different between statements and issue them every once in a while.

Delete all members signatures:

Quote:UPDATE wowbb_users SET user_signature=''

I need a SQL Query to force users that have the "Allow Admins to email me from this board" unchecked in the preferences tab of My Account to be checked.

TO change CURRENT users choice:

Quote:UPDATE wowbb_users SET user_admin_emails='1'

To reset or set a specific member's post count (thanks Michael):

Quote: UPDATE wowbb_users SET user_posts='XXX' WHERE user_name='ZZZZ'


replace the XXX with the exact number you want the post count to be.
replace the ZZZZ with the username (case sensitive) of the person you want to change.

[Twitchin Kitten]

I found this little gem from the old board that may come in handy for someone.

Thanks goes to Bald Eagle for this one!

How do I find the IP of someone who hasn't posted?

Answer:

Actually if you have access to the control panel you can see anyone's ip address that is logged in whether they post or not. Now this changes every night at midnight. It gets reset and the user must create a new session by returning to the site.

Here is an sql statement to use so that the display is more meaningful to you.

Code:

This Contents Of This Code Block Are Hidden For Your Group - Registration and membership will make the contents of this post visible to you.

What this will do is convert user numbers to names so you can read them. By manipulating this query you could also have it show which forum and which topic a user is in (will not show pm or user profile areas).

If you are unsure how to use the CP contact me by pm or email and I will try to explain it to you. Be warned that if yoju do not know what you are doing you can mess things up when using phpMyAdmin to manipulate the database. Also I do not know what everyone's policy is regarding pm's. I used to belong to a forum that read members pm's. At our board we have a policy that pm's are off limits. However in the CP you will have access to all the pm's in the database. Make certain the people you give access to can be trusted to stay out of those tables. Also keep in mind that all system paswords can be changed such that someone you gave permission to could take over your board. If you have multiple user then create a new user that only you know so you can log in if someone tries to get take control of your site. Actually that is a good idea to do even if you do not permit others in the CP.