Handy Mysql queries for UBB.Threads

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.