Useful Threads 7.x Queries

I figure we should make a separate section for our useful Threads7 queries as to keep things separate from the Threads6 stuff (since they're not interchangeable).

ok, i'll try not to hijack again

This is a quick way to make a list of all your themes and how many members have specifically chosen a theme in their profile.

Code


SELECT
CONCAT('[*]',STYLE_NAME,'') AS Style,
CONCAT('(',COUNT(t2.STYLE_NAME),')') as Count 
FROM ubbt_STYLES as t2, 
ubbt_USER_PROFILE as t1 
WHERE t1.USER_STYLE = t2.STYLE_ID 
GROUP BY t2.STYLE_ID 
ORDER BY t2.STYLE_NAME

output after pasting it into the SQL pane in the Database tools is:

Code


[*]matt.DarkBlue	(95)
[*]PaleCoffee	(52)
[*]paradox.Grey	(11)
[*]push.DarkBlue	(26)
[*]sd.DarkDoom	(25)
[*]sd.DarkGreen	(16)
[*]sd.EW.65x.Dark	(114)
[*]sd.Frosted	(77)
[*]sd.MellowBlues	(65)
[*]sd.Ubbdev.tweaked	(78)
[*]Slate	(49)
[*]Sunny.DropShadow	(27)
[*]UbbCentral	(100)
[*]UbbCentral.Classic	(28)
[*]UbbThreads	(81)

you can then copy/paste this between [`list] tags and get:

something like that

note: path/to/your : you know the drill and replace ubbt_ with your board prefix..

note: the links are dead and it's only just for show

Ooh, you combined the two... lol

What mysql command would you use to delete all emailing of banned members' subscriptions?

It's like a combo of these 2 commands:

SELECT * FROM ubbt_USERS WHERE USER_IS_BANNED = "1"

and

UPDATE ubbt_USER_PROFILE SET USER_EMAIL_WATCHLISTS = '0'

This will remove their watch lists period, so email isn't a factor.

Code

DELETE FROM ubbt_WATCH_LISTS
WHERE USER_ID IN
 (SELECT USER_ID FROM ubbt_BANNED_USERS)

This will just stop emailing of watchlist stuff, but not remove the fact that they have watchlists

Code

UPDATE ubbt_USER_PROFILE
SET USER_EMAIL_WATCHLISTS=0
WHERE USER_ID IN
 (SELECT USER_ID FROM ubbt_BANNED_USERS)

Both require mysql 4.1 or greater, for the sub-select

Thank you very much

Wondering this one too:

How do we delete private messages/topics for those users who have been deleted. One of those cleanup things I hope we get in the next version or 2. The users are already deleted, but their private topics/messages are still hanging around.

Originally Posted by AllenAyres

How do we delete private messages/topics for those users who have been deleted. One of those cleanup things I hope we get in the next version or 2. The users are already deleted, but their private topics/messages are still hanging around.

7.3 will be your friend in this wish!

Allen, keep in mind that if one of the other recipients of the PT still has it in their mailbox it isn't yet orphanized; though some users just keep PT's to keep them, so it can be rather interesting...

Originally Posted by blaaskaak

7.3 will be your friend in this wish!

yes, orphaned PMs will be prunable with a Control Panel Clicky

I have a client who says his email notifying .gif blinks even tho he doesn't have any unread emails because he had one from someone that he never read before he deleted the user.

I think Rick posted something about that at central...

Hi,

Can anyone assist with turning on receive admin emails on a group of members whose member number is greater than say 5000

Tried a couple of tests, but no joy

Code

update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>50000

Code

update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>5000

Thanks guys

Did you import a second forum or something equally unusual and now have thousands of posts assigned to someone anonymous?

Well, then, apply these commands to assign them to an actual name if you want them to appear a little more friendly:

Code


UPDATE ubbt_POSTS SET USER_ID = "981" WHERE USER_ID = "1"

Code


UPDATE ubbt_POSTS SET POST_POSTER_NAME = "The Team" WHERE USER_ID = "981"

That takes care of the posts, now to the topics:

Code


UPDATE ubbt_TOPICS SET USER_ID ="981" WHERE USER_ID = "1"

Code


UPDATE ubbt_TOPICS SET USER_ID ="981" WHERE TOPIC_LAST_POSTER_ID = "1"

Code


UPDATE ubbt_TOPICS SET TOPIC_LAST_POSTER_NAME ="The Team" WHERE TOPIC_LAST_POSTER_NAME = "Anonymous"

Cleans it up a bit

How do I find *some* text in the DEFAULT_POST_BODY and change it to *some* other text? I don't want to replace everything in that field, something like I want to trade a smile

for a frown

or something similar?

MySQL offers a replace function.

Code

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Would be great with a 7.3 install where you can use the content rebuilder afterwards to rebuild all the posts.

Yes, thank you

We imported lots of older threads and classic posts that need some cleanup on their smilies and such.

Smart idea Allen, would be nice to finally cleanup some directory's on the server that are there just to serve some smileys in old posts