Handy Mysql queries for UBB.Threads

It’s good to have an SQL toolbox of queries at your disposal. UBB.threads has the Database Tools section, where you can save and re-use helpful queries.
This will be a continually updated list of queries that I either run into or have to create in order to get data out of threads that isn’t normally available via the regular admin interface.
Feel free to suggest ideas or even supply some queries of your own in the comments.
If they make the cut, this post will reflect them. ;)
Ok, on to business. I’ll just start popping them out in no real order. The general format will be the query title, the actual SQL itself and any following notes to give it context.
Last on by group number
SELECT u.USER_ID AS ID, u.USER_LOGIN_NAME AS Login, FROM_UNIXTIME(ud.USER_LAST_VISIT_TIME,'%M %e, %Y - %h:%i %p') AS LastOn FROM ubbt_USERS u LEFT JOIN ubbt_USER_GROUPS ug ON u.USER_ID=ug.USER_ID LEFT JOIN ubbt_USER_DATA ud ON u.USER_ID=ud.USER_ID WHERE ug.GROUP_ID=6 ORDER BY ud.USER_LAST_VISIT_TIME
Select style popularity and create a UBB.Code list
SELECT CONCAT('[*][url=http://yoursite.com/forum/ubbthreads.php?ubb=previewskin&skin=',s.STYLE_ID ,']', STYLE_NAME,'[/url]') AS Style, CONCAT('[color:red](',COUNT(s.STYLE_NAME),')[/color]') as Count FROM ubbt_STYLES s, ubbt_USER_PROFILE up WHERE up.USER_STYLE=s.STYLE_ID GROUP BY s.STYLE_ID ORDER BY s.STYLE_NAME
Ruben pointed out in the comments that this doesn’t mean as much as it could, because most of the users simple never change their preferences and always accept the default. This is true, so to make all users who haven’t selected a style, to ‘look’ like they selected the default one, do the following query first:
UPDATE ubbt_USER_PROFILE SET USER_STYLE=0 WHERE USER_STYLE=''
That will look for anyone who hasn’t set a preference, and make it look like they’ve selected default in their preferences. Then re-run the style popularity and see results for all users.
Find the PM hogs
SELECT COUNT(*) as PMs, u.USER_DISPLAY_NAME FROM ubbt_PRIVATE_MESSAGE_USERS pmu, ubbt_PRIVATE_MESSAGE_TOPICS pmt, ubbt_USERS u WHERE pmu.TOPIC_ID=pmt.TOPIC_ID AND pmu.USER_ID=u.USER_ID GROUP BY pmu.USER_ID HAVING PMs>99 ORDER BY PMs DESC
Trim all user Custom titles to xx characters
Sometimes custom titles can get out of hand with a board setting being too permissive as to length. This query simply trims all titles to a maximum width and adds ellipsis to the end for those that exceed that maximum.
UPDATE ubbt_USER_PROFILE SET USER_CUSTOM_TITLE = CONCAT( SUBSTR( USER_CUSTOM_TITLE, 1, 30 ) , '..' ) WHERE LENGTH( USER_CUSTOM_TITLE ) > 32
For this example I used 32 characters as the limit. Feel free to choose your own and modify both the 30 and 32 values to comply
Update a specific groups name color
Many times on a board, there will be what I’ll call a “sponsor” group. They are accorded with special privileges that the UBB.threads permission matrix has set for them.
Examples could be:
- Custom avatar
- Ability to search forums
- Image in signature
- Can use the Shoutbox
- More private messages allowed
- Can email posts
- etc, etc
Another thing that I typically do is to also give this group a special color. One like the Admins, Global Moderators and Moderators have, but called all their own.
This query can set the name color of all members of a particular group to a specific color, in case some have been missed.
UPDATE ubbt_USER_PROFILE SET USER_NAME_COLOR='#0af' WHERE USER_ID in (SELECT USER_ID from ubbt_USER_GROUPS where GROUP_ID=7)
All you have to substitute for is the 7 value with the desired group number on your board. Additionally, the #0af color value should be changed to the hex value for the desired color.
That’s it.
Comments are closed.