Note: The following is a repost, to allow this to be in a more ordered format. Queries here were either posted by myself or Josh <img src="https://www.ubbcentral.com/boards/images/graemlins/smile.gif" alt="" />
--


I decided to do a search here and post them in an easier to find format. Thanks to all those that have submitted them (Josh, Dave_L, Rick to name a few). Backup your database first to be safe.

1) Query the DB to see how many members will allow the Admin to send them bulk emails.

SELECT COUNT(*) FROM w3t_Users WHERE U_AdminEmails='On'

2) Query to set all users to the default aged threads to be displayed (what is set per forum as that forum's default).

UPDATE w3t_Users
SET U_ActiveThread = "999"

3) Query to set all users to the default parent posts per page shown.

UPDATE w3t_Users
SET U_PostsPer = "10"

4) Query to set all users to the default total posts per page shown (when in flat mode).

UPDATE w3t_Users
SET U_FlatPosts = "10"

5) Query to allow all members to receive email from admins.

UPDATE w3t_Users SET U_AdminEmails = 'On' WHERE U_Number > 1

6) Query to set all members to receive an email notification when they receive a private message.

UPDATE w3t_Users
SET U_Notify = 'On'

7) Query to change the displayed date that a post was made.

UPDATE w3t_Posts SET B_Posted = UNIX_TIMESTAMP('2003-06-14 20:05:00') WHERE B_Number = 123456

Note: Depending on whether it's the last post in a thread or board, you'd have to do similar updates for the main post in the thread and the board that contains the thread.

8) Query to make ALL forums members only.

UPDATE w3t_Boards
SET Bo_Read_Perm = '-1-2-3-', Bo_Write_Perm = '-1-2-3-', Bo_Reply_Perm = '-1-2-3-'

"But if you have any boards that are only supposed to be accessible to admins, mods or other special groups, their permissions would get reset, unless you add an appropriate WHERE clause. "

9) Query to delete all users that have not logged-in since they registered. Submitted by Akd96

DELETE FROM w3t_Users WHERE `U_Laston` = `U_Registered`

10) Query to Update All Users to use the default stylesheet that you've defined in the Theme file Submitted by JoshPet

UPDATE w3t_Users
SET U_StyleSheet = 'usedefault'

11) Query to manually approve a user if the user hasn't verified the account yet.

UPDATE w3t_Users SET U_Approved='yes' WHERE U_Username='whatever'

(whatever would be the username)

11) What would i use to find all members with a certain amount of posts?

SELECT U_Username
FROM w3t_Users
WHERE U_Totalposts = 500

Would find all members with 500 posts.

You also could do like:

SELECT U_Username
FROM w3t_Users
WHERE U_Totalposts > 500
AND U_Totalposts < 600

Would find posts greater than 500 and less than 600.

what command would i use to then delete anyone with a post count of zero?

[6.3.2]

SELECT U_Username,FROM_UNIXTIME(U_Registered)
FROM w3t_Users
WHERE U_Totalposts = 0
AND U_Number > 2
AND U_Registered < UNIX_TIMESTAMP('2003-01-01')
ORDER BY U_Registered

Instead of deleting users with SQL queries, I recommend doing it through the UBB.threads admin page, so that the required cleanup will be done. You could look at admin/dodeleteuser.php or admin/dopurgeusers.php to see what the cleanup consists of.

Reference thread

12) Set all boards to a certain default age

UPDATE w3t_Boards
SET Bo_ThreadAge = "93"

Example shows it set for 3 months

Your choices are:
1 active in the last day
2 active in the last 2 days
7 active in the last week
14 active in the last 2 weeks
21 active in the last 3 weeks
31 active in the last month
93 active in the last 3 months
186 active in the last 6 months
365 active in the last year
all from all dates


Common SQL Error Codes and Solutions:

If you are receiving this "generic error":

SQL ERROR: Database error only visible to forum administrators

Warning: Supplied argument is not a valid MySQL result resource in /rd1/www/ubbthreads/mysql.inc.php on line 121

or similar, the real error message is being masked. You must first either check your MySQL error logs for the actual error, or edit the mysql.inc.php file to allow it to be displayed, so that you can track down what's wrong.

1. Backup the mysql.inc.php file first.
2. Edit mysql.inc.php file with a text editor
3. Find this near the very bottom:

if ( ($user['U_Status'] != "Administrator")
&& ( (!stristr($What,"createtable")) && (!stristr($What,"altertable")) && (!stristr($What,"ubbimport.php")) ) ) {
$error = "Database error only visible to forum administrators";
}
else {
$this->errordesc = mysql_error();
}

4. Change to this:

// if ( ($user['U_Status'] != "Administrator")
// && ( (!stristr($What,"createtable")) && (!stristr($What,"altertable")) && (!stristr($What,"ubbimport.php")) ) ) {
// $error = "Database error only visible to forum administrators";
// }
// else {
$this->errordesc = mysql_error();
// }


Now the screen will display the actual error, which you can report. Once the problem is resolved, return the mysql.inc.php file to the original state so that errors are not readily displayed to everyone. <img border="0" title="" alt="[Wink]" src="images/icons/wink.gif" />


Common Errors:

Can't open file: 'w3t_Online.MYI'. (errno: 145)

Error Number 145 = Table was marked as crashed and should be repaired.

Generally this query will fix this:


REPAIR TABLE w3t_Online


*Substitue whatever the table name is for "w3t_Online" above to repair whatever database table is corrupted.


SQL ERROR: Unable to do_query: blah blah blah
Got error 28 from table handler

Error Number 28 = No space left on device


Verify that your webserver or the partition where the database is stored on your server is not out of space. Once this is corrected, you may need to run the REPAIR TABLE command listed above, as corruption sometimes occurs when MySQL attempts to write data to a full drive.


SQL ERROR: Unable to do query: Blah blah blah Got error 127 from table handler

Error 127 = Unknown Error / Record File Crashed


You can generally look at what table or tables it was trying to Query and run the Repair Table command for that table.

For example, if the query throwing the error is the w3t_Posts table, you can try entering this SQL Query:

REPAIR TABLE w3t_Posts


If this does not work, you may need to ask your webhost to run myisamchk on your database. You can also try dumping and restoring the database with the mysqldump command.

Tips that can reduce the load on your MySQL Server:
  • Use partial new post tracking instead of full new post tracking in your config file.
  • Set the theme file to show a smaller number of topics per page on the postlist screen and smaller number of posts to be shown in flat mode (recommend 10 each). This will reduce the load that anonymous users have on the forum.
  • If your theme file was set to a large number of posts per page - you can update all existing members to a lower amount with this query:
    (Backup your database first)
    UPDATE w3t_Users
    SET U_PostsPer = 10,
    U_FlatPosts = 10

    Users can still select a higher amount in their display prefs, but for the masses this can reduce the overall load.
  • adjust the settings of each forum to show a reduced range of topics. Example if they are all set to show "all topics" - set each forum to show only topics from the last 30 days etc...

--
Website Development and Management
www.jcswebdev.com