Previous Thread
Next Thread
Print Thread
Rate Thread
#314712 07/13/2007 9:34 PM
Joined: Jan 2000
Posts: 5,833
Likes: 20
UBBDev / UBBWiki Owner
Time Lord
UBBDev / UBBWiki Owner
Time Lord
Joined: Jan 2000
Posts: 5,833
Likes: 20
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 - Putting Dev into UBB.threads
Company: VNC Web Services - UBB.threads Scripts and Scripting, Install and Upgrade Services, Site and Server Maintenance.
Forums: A Gardeners Forum, Scouters World, and UGN Security
UBB.Threads: My UBB Themes, My UBB Scripts
Sponsored Links
Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
ok, i'll try not to hijack again smile

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

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



something like that wink

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 wink

Joined: Jan 2000
Posts: 5,833
Likes: 20
UBBDev / UBBWiki Owner
Time Lord
UBBDev / UBBWiki Owner
Time Lord
Joined: Jan 2000
Posts: 5,833
Likes: 20
Ooh, you combined the two... lol


UBB.Dev - Putting Dev into UBB.threads
Company: VNC Web Services - UBB.threads Scripts and Scripting, Install and Upgrade Services, Site and Server Maintenance.
Forums: A Gardeners Forum, Scouters World, and UGN Security
UBB.Threads: My UBB Themes, My UBB Scripts
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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 wavey
- What Drives You?
Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
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 wink

Sponsored Links
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Thank you very much smile


- Allen wavey
- What Drives You?
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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 wavey
- What Drives You?
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
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!

Joined: Jan 2000
Posts: 5,833
Likes: 20
UBBDev / UBBWiki Owner
Time Lord
UBBDev / UBBWiki Owner
Time Lord
Joined: Jan 2000
Posts: 5,833
Likes: 20
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 - Putting Dev into UBB.threads
Company: VNC Web Services - UBB.threads Scripts and Scripting, Install and Upgrade Services, Site and Server Maintenance.
Forums: A Gardeners Forum, Scouters World, and UGN Security
UBB.Threads: My UBB Themes, My UBB Scripts
Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
Originally Posted by blaaskaak
7.3 will be your friend in this wish!

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

Sponsored Links
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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 wavey
- What Drives You?
Joined: Jan 2000
Posts: 5,833
Likes: 20
UBBDev / UBBWiki Owner
Time Lord
UBBDev / UBBWiki Owner
Time Lord
Joined: Jan 2000
Posts: 5,833
Likes: 20
I think Rick posted something about that at central...


UBB.Dev - Putting Dev into UBB.threads
Company: VNC Web Services - UBB.threads Scripts and Scripting, Install and Upgrade Services, Site and Server Maintenance.
Forums: A Gardeners Forum, Scouters World, and UGN Security
UBB.Threads: My UBB Themes, My UBB Scripts
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
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 frown


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
Code
update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>50000

Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
Code
update ubbt_USER_PROFILE set USER_ACCEPT_ADMIN_EMAILS="On" where USER_ID>5000

Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
Thanks guys smile


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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 smile


- Allen wavey
- What Drives You?
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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 smile for a frown frown or something similar?


- Allen wavey
- What Drives You?
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
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.

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Yes, thank you thumbsup

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


- Allen wavey
- What Drives You?
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
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 smile


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Posts: 70
Joined: January 2007
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)