Useful Threads 7.x Queries

You are not logged in. [Log In] ubbDev » Forums » Community Development » How To/Site Help Library » Useful Threads 7.x Queries

Page 1 of 2

Topic Options

#314712 - 07/13/07 09:34 PM Useful Threads 7.x Queries
Gizmo Wizard Registered: 01/10/00 Posts: 5122 Loc: Portland, OR, USA Add to del.icio.us Digg it	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). _________________________ UBB.Dev - Where you too can render your UBB install completely useless... UGN Security, Elite Web Gamers & VNC Web Design Owner
Top

#314713 - 07/13/07 09:45 PM

Re: Useful Threads 7.x Queries [Re: Gizmo]

sirdude Offline

Bada bing!
Enthusiast

Registered: 11/08/03
Posts: 439
Loc: SoCal

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('[*][url=http://path/to/threads/ubbthreads.php?ubb=previewskin&skin=',t2.STYLE_ID,']',STYLE_NAME,'[/url]') AS Style,
CONCAT('[color:red](',COUNT(t2.STYLE_NAME),')[/color]') 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:

[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=6]matt.DarkBlue[/url]	[color:red](95)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=4]PaleCoffee[/url]	[color:red](52)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=12]paradox.Grey[/url]	[color:red](11)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=20]push.DarkBlue[/url]	[color:red](26)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=22]sd.DarkDoom[/url]	[color:red](25)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=21]sd.DarkGreen[/url]	[color:red](16)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=2]sd.EW.65x.Dark[/url]	[color:red](114)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=16]sd.Frosted[/url]	[color:red](77)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=17]sd.MellowBlues[/url]	[color:red](65)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=15]sd.Ubbdev.tweaked[/url]	[color:red](78)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=3]Slate[/url]	[color:red](49)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=5]Sunny.DropShadow[/url]	[color:red](27)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=8]UbbCentral[/url]	[color:red](100)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=9]UbbCentral.Classic[/url]	[color:red](28)[/color]
[*][url=http://yourdomain.com/ubbthreads/ubbthreads.php?ubb=previewskin&skin=1]UbbThreads[/url]	[color:red](81)[/color]

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

_________________________

A taxpayer voting for Obama is like a chicken voting for Colonel Sanders.

Top

#314714 - 07/13/07 09:50 PM Re: Useful Threads 7.x Queries [Re: sirdude]
Gizmo Wizard Registered: 01/10/00 Posts: 5122 Loc: Portland, OR, USA	Ooh, you combined the two... lol _________________________ UBB.Dev - Where you too can render your UBB install completely useless... UGN Security, Elite Web Gamers & VNC Web Design Owner
Top

#316162 - 10/27/07 10:52 PM Re: Useful Threads 7.x Queries [Re: Gizmo]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	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' _________________________ - Allen - What Drives You?
Top

#316164 - 10/28/07 02:19 AM Re: Useful Threads 7.x Queries [Re: AllenAyres]
sirdude Bada bing! Enthusiast Registered: 11/08/03 Posts: 439 Loc: SoCal	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 _________________________ A taxpayer voting for Obama is like a chicken voting for Colonel Sanders.
Top

#316165 - 10/28/07 10:46 AM Re: Useful Threads 7.x Queries [Re: sirdude]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	Thank you very much _________________________ - Allen - What Drives You?
Top

#316166 - 10/28/07 11:51 AM Re: Useful Threads 7.x Queries [Re: sirdude]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	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. _________________________ - Allen - What Drives You?
Top

#316167 - 10/28/07 12:58 PM Re: Useful Threads 7.x Queries [Re: AllenAyres]
blaaskaak Member Registered: 02/25/07 Posts: 299 Loc: The Netherlands	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! _________________________
Top

#316168 - 10/28/07 04:11 PM Re: Useful Threads 7.x Queries [Re: blaaskaak]
Gizmo Wizard Registered: 01/10/00 Posts: 5122 Loc: Portland, OR, USA	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... _________________________ UBB.Dev - Where you too can render your UBB install completely useless... UGN Security, Elite Web Gamers & VNC Web Design Owner
Top

#316169 - 10/28/07 06:51 PM Re: Useful Threads 7.x Queries [Re: blaaskaak]
sirdude Bada bing! Enthusiast Registered: 11/08/03 Posts: 439 Loc: SoCal	Originally Posted By: blaaskaak 7.3 will be your friend in this wish! yes, orphaned PMs will be prunable with a Control Panel Clicky _________________________ A taxpayer voting for Obama is like a chicken voting for Colonel Sanders.
Top

#316174 - 10/28/07 09:49 PM Re: Useful Threads 7.x Queries [Re: sirdude]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	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. _________________________ - Allen - What Drives You?
Top

#316176 - 10/29/07 12:01 AM Re: Useful Threads 7.x Queries [Re: AllenAyres]
Gizmo Wizard Registered: 01/10/00 Posts: 5122 Loc: Portland, OR, USA	I think Rick posted something about that at central... _________________________ UBB.Dev - Where you too can render your UBB install completely useless... UGN Security, Elite Web Gamers & VNC Web Design Owner
Top

#316377 - 11/27/07 07:24 AM Re: Useful Threads 7.x Queries [Re: Gizmo]
Ian_W Veteran Registered: 02/22/02 Posts: 2574 Loc: England	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 _________________________ Fans Focus - Focusing on Fans of Sport (Okay - mainly football (the British variety at the moment - but expanding all the time....)
Top

#316379 - 11/27/07 07:57 AM Re: Useful Threads 7.x Queries [Re: Ian_W]
blaaskaak Member Registered: 02/25/07 Posts: 299 Loc: The Netherlands	Code: update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>50000 _________________________
Top

#316382 - 11/27/07 10:01 AM Re: Useful Threads 7.x Queries [Re: blaaskaak]
sirdude Bada bing! Enthusiast Registered: 11/08/03 Posts: 439 Loc: SoCal	Code: update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>5000 _________________________ A taxpayer voting for Obama is like a chicken voting for Colonel Sanders.
Top

#316383 - 11/27/07 11:58 AM Re: Useful Threads 7.x Queries [Re: sirdude]
Ian_W Veteran Registered: 02/22/02 Posts: 2574 Loc: England	Thanks guys _________________________ Fans Focus - Focusing on Fans of Sport (Okay - mainly football (the British variety at the moment - but expanding all the time....)
Top

#316813 - 03/26/08 01:54 PM Re: Useful Threads 7.x Queries [Re: Ian_W]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	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 _________________________ - Allen - What Drives You?
Top

#316814 - 03/26/08 02:09 PM Re: Useful Threads 7.x Queries [Re: AllenAyres]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	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? _________________________ - Allen - What Drives You?
Top

#316819 - 03/27/08 10:56 AM Re: Useful Threads 7.x Queries [Re: AllenAyres]
blaaskaak Member Registered: 02/25/07 Posts: 299 Loc: The Netherlands	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. _________________________
Top

#316821 - 03/27/08 11:26 AM Re: Useful Threads 7.x Queries [Re: blaaskaak]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25432 Loc: Texas	Yes, thank you We imported lots of older threads and classic posts that need some cleanup on their smilies and such. _________________________ - Allen - What Drives You?
Top

Page 1 of 2

Previous Topic

Index

Next Topic

Hop to: