Backup your database first to be safe. <br /> <br />1) Query the DB to see how many members will allow the Admin to send them bulk emails. <br /> <br />SELECT COUNT(*) FROM w3t_Users WHERE U_AdminEmails='On' <br /> <br />2) Query to set all users to the default aged threads to be displayed (what is set per forum as that forum's default). <br /> <br />UPDATE w3t_Users <br />SET U_ActiveThread = "999" <br /> <br />3) Query to set all users to the default parent posts per page shown. <br /> <br />UPDATE w3t_Users <br />SET U_PostsPer = "10" <br /> <br />4) Query to set all users to the default total posts per page shown (when in flat mode). <br /> <br />UPDATE w3t_Users <br />SET U_FlatPosts = "10" <br /> <br />5) Query to allow all members to receive email from admins. <br /> <br />UPDATE w3t_Users SET U_AdminEmails = 'On' WHERE U_Number > 1 <br /> <br />6) Query to set all members to receive an email notification when they receive a private message. <br /> <br />UPDATE w3t_Users <br />SET U_Notify = 'On' <br /> <br />7) Query to change the displayed date that a post was made. <br /> <br />UPDATE w3t_Posts SET B_Posted = UNIX_TIMESTAMP('2003-06-14 20:05:00') WHERE B_Number = 123456 <br /> <br />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. <br /> <br />8) Query to make ALL forums members only. <br /> <br />UPDATE w3t_Boards <br />SET Bo_Read_Perm = '-1-2-3-', Bo_Write_Perm = '-1-2-3-', Bo_Reply_Perm = '-1-2-3-' <br /> <br />"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. " <br /> <br />9) Query to delete all users that have not logged-in since they registered. Submitted by Akd96 <br /> <br />DELETE FROM w3t_Users WHERE `U_Laston` = `U_Registered` <br /> <br />10) Query to Update All Users to use the default stylesheet that you've defined in the Theme file Submitted by JoshPet <br /> <br />UPDATE w3t_Users <br />SET U_StyleSheet = 'usedefault' <br /> <br />11) Query to manually approve a user if the user hasn't verified the account yet. <br /> <br />UPDATE w3t_Users SET U_Approved='yes' WHERE U_Username='whatever' <br /> <br />(whatever would be the username) <br /> <br /> <br /> <br />Thanks to all who have posted these in the past. (Dave_L, Rick, me, etc... <img src="/forum/images/graemlins/wink.gif" alt="" /> ) and to Mr.CSS for the idea. <br /> <br /> <br />

Can you give me a command to: <br /> <br />delete all pm's made in the last day, or a certain day, or that contain a certain word? <br /> <br />The command below where given to me in the past... <br /> <br />SELECT DISTINCT B_IP <br />FROM w3t_Posts <br />WHERE B_PosterID = $User (replace user's user Number) <br /> <br />that gives all the IP's a user has posted under. <br /> <br /> SELECT U_RegIP <br /> FROM w3t_Users <br /> WHERE U_Number = $User (replace user's user Number) <br /> <br />that gives you the IP the person registered with. <br /> <br />SELECT B_PosterID <br />FROM w3t_Posts <br />WHERE B_IP = 'xxx.xxx.xxx.xxx' (replace IP number here) <br /> <br />that gives you all the people who have posted under a particular IP address. It's usually more helpful if you chop off a couple of the last numbers and use '%' at the end, so it will pull a slightly larger range of IP's. <br /> <br />

I helped you with this - as it was a bit more complicated than that as the user had already been deleted.<br /><br />Since all the PMs were already changed to user #1 - had to do a query like this:<br /><br />DELETE FROM w3t_Messages<br />WHERE M_Subject LIKE 'the subject here'<br /><br />Had the user not been deleted, then we could have done<br /><br />DELETE FROM w3t_Messages<br />WHERE M_Sender = $user (replace user's user number)<br /><br /><br />However, all the people that had PMs will still flash that they have a pm, but when they view the list of private messages, the counter will get reset and the flashing will stop. So it may confuse the users a bit looking for a PM that they don't have. But in your case of 6000+ spam PMs... there was really no other easy way. <img src="/forum/images/graemlins/smile.gif" alt="" />

how do I update all users with 100+ posts to a new usergroup?<br /><br />I'm using your pm restrictor mod and I'm using a usergroup that not many people are a part of - works for the new people, not the old people <img src="/forum/images/graemlins/crazy.gif" alt="" />

OK - <br /><br />Something like this:<br /><br />UPDATE w3t_Users<br />SET U_Groups = '-3-5-'<br />WHERE U_Totalposts > 100<br />AND U_Status = 'User'<br /><br /><br />Note: That will change whatever user groups that they had - to -3- (the user group) and -5- (or whatever other group you need).<br /><br />If you need to append the new user group to what they have.... I'm nor sure exactly what needs to be done. I'd write a little script for it. (Contact me and I'll help you AA).<br /><br />RandyJG posted here about the use of CONCAT but I'm not familiar with it myself. <img src="/forum/images/graemlins/smile.gif" alt="" /><br /><br />

thank you, that covered it close enough for what we needed <img src="/forum/images/graemlins/smile.gif" alt="" />

Nice one Josh, lots of good stuff there! <img src="/forum/images/graemlins/smile.gif" alt="" /><br /><br />One question, how do you reverse No.8 and change it so that all guests can view all the forums?<br /><br />Cheers<br />Hugh

Change the desired permission strings from '-1-2-3-' to '-1-2-3-4-'. The same disclaimer applies. That will change the permissions for all the forums unless you qualify the query with an appropriate WHERE clause. It also doesn't take into account any custom groups (5, 6, etc.) that you may have added.

Top stuff, thanks Dave! <img src="/forum/images/graemlins/smile.gif" alt="" />

This is a nice compilation of useful queries. I made a script which shows them all and made a link to it from the admin menu. But since saving queries is a part of 6.4 I'm guessing it might not be of use for anybody?<br /><br />I also have a question about deleting users who have never logged in, will this leave traces of them in other tables? If so, it might be better to change the purgeusers script so that it is possible to select whether to only delete users who have never logged in instead.

I think that if a user has never logged in, then there will only be rows in two tables: w3t_Users and w3t_Messages (the welcome message).

OK, that sounds good, I could probably check it out a bit before I do the actual removing. Does someone have a script or query to remove orphan rows in w3t_Messages?

Other that would ben ice to know<br /><br />How to delete all PM's older than 30 days<br /><br />How to delete all pm's that have a certain word in them<br /><br />How to replace / change a pm a user sent to all members<br /><br />How to make all visitors make a donation or purchase a subscription :-)

How to delete all PM's older than 30 days<br /><br />DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP('2003-10-16')<br /><br />(This will delete all PM:s sent before the given date, which is in the format YYYY-MM-DD.)<br /><br />DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 MONTH) )<br /><br />(This should delete all PM:s that are older than 1 month, regardless of what date it is.)<br /><br />How to delete all pm's that have a certain word in them<br /><br />DELETE FROM w3t_Messages WHERE M_Message LIKE '%yourwordhere%'<br /><br />(This will remove all PM:s where the word exists, regardless of if it's only part of a word.)<br /><br />How to replace / change a pm a user sent to all members<br /><br />UPDATE w3t_Messages SET M_Message = 'Your new message' WHERE M_Subject = 'Subject of your PM' AND M_Sender = 2<br /><br />(All PM:s sent by user number 2 will have the message replaced with the new message.)<br /><br />How to make all visitors make a donation or purchase a subscription :-)<br /><br />UPDATE w3t_Users SET U_Donation = 'yes'<br /><br /><br />Disclaimer: Please make a backup of your database before trying of any of these queries. Also, change "w3t_" to whatever you have set as your database prefix. Some queries might not work in all versions of UBB.threads.

Thank you very much. <br />The last one may need some further tweaking :-) <br /> <br />Is there a way to delete all posts made by a user in the past day, two days, 10 days?

DELETE FROM w3t_Messages WHERE M_Uid = userid AND M_Sent > UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 DAY) )<br /><br />Change userid to the U_Number of the user you want to remove messages from. Change 1 to how many days you want to remove from.<br /><br />If you want to go by username, and you are running MySQL 4, you could do this:<br /><br />DELETE w3t_Messages FROM w3t_Messages AS m, w3t_Users AS u WHERE m.M_Uid = u.U_Number AND u.U_Username = 'Gardener' AND M_Sent > UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 10 DAY) )<br /><br />As always, please do a backup of your data before running these queries. Don't want you to loose anything because of a spelling mistake of mine or something.

For everyone using this stuff: I will stress EXTREME Caution with deleting stuff manually in the Database and would instead encourage you to use built in tools for deleting stuff. <br /><br />Particularly Users, Posts, Groups etc.... never delete them directly unless you know all the other stuff that needs to be updated as well. Check the admin scripts that delete users/posts etc... to see what other stuff needs to be updated as well. <img src="http://www.ubbdev.com/forum/images/graemlins/wink.gif" alt="" /><br /><br />If you delete the wrong stuff it can cause threads/forums or more to simply not appear properly, as JOINs can not match up.<br /><br />So excercise EXTREME caution when doing SQL Queries directly in the Database, as they are usually unreversable and/or can take hours to diagnose/fix if stuff gets out of wack. <br /><br /><img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

That is very true. Also, don't forget to do a backup if you do decide to change anything. I know people who have deleted the entire basis of a company by having a < the wrong way and no proper backups. Well, they managed to fix it in the end by hiring Microsoft consultants from abroad which costed $$$++.

I do agree with the warning. <br />I was playing with the commands, did not back up, and ended up deleting all trace, all posts, and all pm's made by our top poster :-( and who knows what else :-) <br /> <br />Backing up and restoring is not very easy either. <br />I had a back up at the server (have to hard disks, the second for backup every 12 hours) but it was of no use... before I could get in touch with my isp, before they answered, before i confirmed, and so on... it may take a couple of days... <br /> <br />Could have been worst :-)

I would like to know how to change the number of posts and the registration date of a member<br /><br />Thanks