Previous Thread
Next Thread
Print Thread
Rate Thread
Page 1 of 3 1 2 3
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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)



Thanks to all who have posted these in the past. (Dave_L, Rick, me, etc... ) and to Mr.CSS for the idea.



Last edited by JoshPet; 09/11/2003 11:49 AM.
Sponsored Links
Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
Can you give me a command to:

delete all pm's made in the last day, or a certain day, or that contain a certain word?

The command below where given to me in the past...

SELECT DISTINCT B_IP
FROM w3t_Posts
WHERE B_PosterID = $User (replace user's user Number)

that gives all the IP's a user has posted under.

SELECT U_RegIP
FROM w3t_Users
WHERE U_Number = $User (replace user's user Number)

that gives you the IP the person registered with.

SELECT B_PosterID
FROM w3t_Posts
WHERE B_IP = 'xxx.xxx.xxx.xxx' (replace IP number here)

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.


Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
I helped you with this - as it was a bit more complicated than that as the user had already been deleted.

Since all the PMs were already changed to user #1 - had to do a query like this:

DELETE FROM w3t_Messages
WHERE M_Subject LIKE 'the subject here'

Had the user not been deleted, then we could have done

DELETE FROM w3t_Messages
WHERE M_Sender = $user (replace user's user number)


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.

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 update all users with 100+ posts to a new usergroup?

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


- Allen wavey
- What Drives You?
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
OK -

Something like this:

UPDATE w3t_Users
SET U_Groups = '-3-5-'
WHERE U_Totalposts > 100
AND U_Status = 'User'


Note: That will change whatever user groups that they had - to -3- (the user group) and -5- (or whatever other group you need).

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).

RandyJG posted here about the use of CONCAT but I'm not familiar with it myself.


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, that covered it close enough for what we needed


- Allen wavey
- What Drives You?
Joined: Sep 2003
Posts: 67
Power User
Power User
Joined: Sep 2003
Posts: 67
Nice one Josh, lots of good stuff there!

One question, how do you reverse No.8 and change it so that all guests can view all the forums?

Cheers
Hugh

Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768
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.

Joined: Sep 2003
Posts: 67
Power User
Power User
Joined: Sep 2003
Posts: 67
Top stuff, thanks Dave!

Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
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?

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.

Sponsored Links
Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768
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).

Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
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?

Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
Other that would ben ice to know

How to delete all PM's older than 30 days

How to delete all pm's that have a certain word in them

How to replace / change a pm a user sent to all members

How to make all visitors make a donation or purchase a subscription :-)

Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
How to delete all PM's older than 30 days

DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP('2003-10-16')

(This will delete all PM:s sent before the given date, which is in the format YYYY-MM-DD.)

DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 MONTH) )

(This should delete all PM:s that are older than 1 month, regardless of what date it is.)

How to delete all pm's that have a certain word in them

DELETE FROM w3t_Messages WHERE M_Message LIKE '%yourwordhere%'

(This will remove all PM:s where the word exists, regardless of if it's only part of a word.)

How to replace / change a pm a user sent to all members

UPDATE w3t_Messages SET M_Message = 'Your new message' WHERE M_Subject = 'Subject of your PM' AND M_Sender = 2

(All PM:s sent by user number 2 will have the message replaced with the new message.)

How to make all visitors make a donation or purchase a subscription :-)

UPDATE w3t_Users SET U_Donation = 'yes'


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.

Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
Thank you very much.
The last one may need some further tweaking :-)

Is there a way to delete all posts made by a user in the past day, two days, 10 days?

Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
DELETE FROM w3t_Messages WHERE M_Uid = userid AND M_Sent > UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 DAY) )

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.

If you want to go by username, and you are running MySQL 4, you could do this:

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) )

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.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

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.

If you delete the wrong stuff it can cause threads/forums or more to simply not appear properly, as JOINs can not match up.

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.


Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
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 $$$++.

Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
I do agree with the warning.
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 :-)

Backing up and restoring is not very easy either.
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...

Could have been worst :-)

Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
I would like to know how to change the number of posts and the registration date of a member

Thanks

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
UPDATE w3t_Users
SET U_Totalposts = 'NEWTOTALHERE'
WHERE U_Username = 'USERNAMEHERE'

UPDATE w3t_Users
SET U_Registered = UNIX_TIMESTAMP('2003-10-16')
WHERE U_Username = 'USERNAMEHERE'

Change the date to what you want

Joined: May 2002
Posts: 153
Member
Member
Offline
Joined: May 2002
Posts: 153
Thanks.
Tryed that but did not work.
Is it the same if username and displayed name are not the same?

also... is there a way to show the real number of posts for a user... this user had a problem and the number of posts changed... would really like to show the reasl number of posts

Joined: Aug 2002
Posts: 239
Member
Member
Joined: Aug 2002
Posts: 239
What would a query be to look up people who have registered but not yet verified their e-mail addies to be real?


Some people read their stars..... I choose to write my own
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
U_Username is DisplayName

U_LoginName is the loginname

Those queries above will do exactly what you asked for. You say they dont what is the error you get. You can also suppliment the WHERE U_Username = 'USERNAMEHERE' to WHERE U_Number = 'usernumberhere' Make sure you are not forgetting the slashes around the name or number in the where clause.

As far as getting a post count

SELECT COUNT(*)
FROM w3t_Posts
WHERE B_PosterId ='USERNUMBERHERE'

Then to update the users total

UPDATE w3t_Users
SET U_TotalPosts = 'TOTALHERE'
WHERE U_Number = 'USERNUMBERHERE'

Joined: Jan 2003
Posts: 263
Member
Member
Joined: Jan 2003
Posts: 263
Which query would I need to change the url in U_Picture (Users) ?

Example "http://www.domain1.com/forum/userpics/2.jpg"

should be changed in

Example "http://www.name2.com/forum/userpics/2.jpg"

Thanks!

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
You changed the config avatar directory in the setup? This is the directory where pictures are.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
No the URL to the avatar is stored in U_Picture - the value in the config file is just for uploads and used when it's being stored.

This should do it:

UPDATE w3t_Users
SET U_Picture = 'http://whatever/what/userpic/2.jpg'
WHERE U_Number = xxx

Put the usernumber of xxx


Joined: Jan 2003
Posts: 263
Member
Member
Joined: Jan 2003
Posts: 263
Thanks Josh, but your query would not do the trick for me..

I want to change the picture URL for all users, so I only need to change "domain1.com" to "name2.com".

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
you need a query that first grabs the link from the database in a query and then pulls the substring of the first domain and inserts the substring of the second domain and then inserts that into the database... but thats a little dangerous if not used correctly..

Last edited by scroungr; 01/08/2004 9:36 AM.
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
This will do what you want Pasqualist and is courtesy of Master Scream when I asked him about this issue a while back.

Now this query is mostly for those wanting to update their forums from UBB to Threads and change links in the post bodies as this is where this issue is likely to occur.

UPDATE w3t_Posts SET B_Body = REPLACE(B_Body, 'cgi-bin/ubb','ubbthreads')

Now you can change this query to suit your needs by doing the following to it.

UPDATE w3t_Users SET U_Picture = REPLACE(U_Picture, 'www.domain1.com','www.name2.com')

Now remember to backup your database before doing this but I have ran the first query on my install and it worked fine. It should automatically update the part of the string you want.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
even better

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
LOL Yeah I had asked the MASTER a while back as I still had links in posts to my old ubb install graemlins etc

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
forgot all about the mysql REPLACE command

Joined: Dec 2003
Posts: 18
Newbie
Newbie
Offline
Joined: Dec 2003
Posts: 18
Ok....I have a request since I'm in the "throws" of re-installing.

Would it be possible to repost something as an ADMIN (with the boards closed) and then change the ADMIN association to the original poster? This is so I can re-post threads lost with my ... huhummm... re-installation?

Thanks,
Patrice

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
think it's easier to log in as the user... you can switch to them in the admin panel... problem is boards would have to be open not closed..

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
If your missing threads from your threads database after the install of your UBB classic database conversion that malfunctioned after an import and the board went down after running threads for a few days then you can use PhpMyAdmin to input users and posts after the original conversion.

I take it now you have a correct conversion running. Just use PHPMYADMIN to import any new users into Threads that are missing from your last Threads backup when it was working before it crashed.

You then can do the same thing with the posts table.

INSERT INTO `w3t_Users` VALUES ( blah blah blah);

INSERT INTO `w3t_Posts` VALUES ( blah blah blah);

Your backups when you compare should guide you further where the cutoff is. Problem is if you opened your board back up now after the second import then you cant do this as post numbers and user numbers will have been used that you are trying to import.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
yeah but Omego the way it sounds..

[]
Would it be possible to repost something as an ADMIN (with the boards closed) and then change the ADMIN association to the original poster? This is so I can re-post threads lost with my ... huhummm... re-installation?
[/]

is that she wants to post those lost as herself then go in later and change the name of who posted it to the original person cause she lost them...

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
If she lost the posts and does not have a backup to do what I am saying then how is she going to know what to post to do what your saying

See my point.

Joined: Dec 2003
Posts: 18
Newbie
Newbie
Offline
Joined: Dec 2003
Posts: 18
Glutton for punishment. My solution. Probably not the best, but it got the job done. I ended up make all the forums "read-only" with admin and MODERATOR read/write. Made the user a temporary moderator, logged in as them and reposted their thread and demoted them. I'm cross-eyed, but it's done. Whew. Think I'll BACK IT UP now!

-Patrice

Last edited by CurlGirl; 01/09/2004 11:16 AM.
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
You did the things the hard way. Reporting one thread at a time. You could have inserted the new posts via PHPMYADMIN in one step.

Joined: Dec 2003
Posts: 18
Newbie
Newbie
Offline
Joined: Dec 2003
Posts: 18
Believe me. It would have taken ME longer to do that and then fix what I screwed up doing that...right now I'm a bit gun shy. I knew that I couldn't screw things up too bad this way. Me and sleep deprivation are a dangerous mix. Stupidity abounds! Thanks anyway!

Joined: Aug 2002
Posts: 239
Member
Member
Joined: Aug 2002
Posts: 239
What query would I need to find out who all belongs to a certain group?


Some people read their stars..... I choose to write my own
Joined: Aug 2002
Posts: 239
Member
Member
Joined: Aug 2002
Posts: 239
oops... Never mind.... lol Figured out I could do it from the AP.....


Some people read their stars..... I choose to write my own
Joined: Jan 2003
Posts: 141
Journeyman
Journeyman
Offline
Joined: Jan 2003
Posts: 141
I'd like to know that question. I demoted a moderator and it still showed him in group "2" and he could still read the forums in the admin section. I ran a querie to make his groups right but I can't seem to figure out the correct querie to see if anyone else is in that group that isn't supposed to be.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
SELECT `U_Username` , `U_Groups`
FROM `w3t_Users`
WHERE `U_Groups`
LIKE "%-2-%"

change the FROM `w3t_Users`
to your table extension, mine's w3t_
also change the %-2-% to whichever group your looking for.

Joined: Jan 2003
Posts: 141
Journeyman
Journeyman
Offline
Joined: Jan 2003
Posts: 141
Thanks!

Joined: Apr 2003
Posts: 359
Enthusiast
Enthusiast
Joined: Apr 2003
Posts: 359
My glasses need cleaning again? Is there a query to run that I can input an IP and find out who all (Username or Login Name) has been using that particular IP?

Thanks...

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
SELECT `U_Username`, `U_Group`, `U_RegIP`, `B_IP`, `B_PosterId`
FROM `w3t_Posts`, `w3t_Users`
WHERE (((`B_IP` = '192.168.1.1') AND (`B_PosterId` = `U_Number`)) OR (`B_IP` = '192.168.1.1'))

change I192.168.1.1 to what your loking for
and then stand back cause on large boards this MAY time out since it will go through all the posts looking for IP addresses of the poster and matching that up where it can to the User and if an anon it won;t spit out a Username.

Joined: Apr 2003
Posts: 359
Enthusiast
Enthusiast
Joined: Apr 2003
Posts: 359
I ran this query and it came up with errors:

SELECT `U_Username`, `U_Group`, `U_RegIP`, `B_IP`, `B_PosterId`
FROM `w3t_Posts`, `w3t_Users`
WHERE (((`B_IP` = '199.253.23.1') AND (`B_PosterId` = `U_Number`)) OR (`B_IP` = '199.253.23.1'))

The IP listed is the one in question from my site.

Here are the errors that were returned:

SQL ERROR: Unable to do_query: SELECT `U_Username`, `U_Group`, `U_RegIP`, `B_IP`, `B_PosterId` FROM `w3t_Posts`, `w3t_Users` WHERE (((`B_IP` = '199.253.23.1') AND (`B_PosterId` = `U_Number`)) OR (`B_IP` = '199.253.23.1'))
Unknown column 'U_Group' in 'field list'
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/smoknz28/public_html/ubbthreads/mysql.inc.php on line 167

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/smoknz28/public_html/ubbthreads/mysql.inc.php on line 133

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
SELECT `U_Username`, `U_Groups`, `U_RegIP`, `B_IP`, `B_PosterId`
FROM `w3t_Posts`, `w3t_Users`
WHERE (((`B_IP` = '199.253.23.1') AND (`B_PosterId` = `U_Number`)) OR (`B_IP` = '199.253.23.1'))


sorry I typoed.. U_Group should be U_Groups

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've got a question:

I imported a ubb.classic to threads. Because of an issue with custom forum headers I needed to empty the posts table and re-import them. Now all posts have the person who posted the message as 'unregistered':

http://www.praisecafe.org/forum/ubbthreads.php

Is there a way to re-associate the poster within the post with the actual user profile?

note: this is a test forum, we plan to do this actually on a live site for someone else...


- Allen wavey
- What Drives You?
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369

Not sure how that can be done on a large scale. But to do it individually to a post - this is the query to use:

UPDATE w3t_Posts
SET B_Reged = 'y',
B_PosterId = number


B_Reged is set to 'y' for "yes" meaning they are registered and B_PosterId is the user number.

The name is being stored in the B_AnonName field - I'm not sure if there's a way to do a query which matches them up and would let you update the whole thing at a time.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Allen - untested, but name the attached .php and put it in your threads directory - and give it a run. See if it updates the posts to match the user database.
Attachments
109215-updateanonposts.txt (0 Bytes, 337 downloads)

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Done! 3 post records updated!

Thanks, not sure why it's only fixing 3 Maybe the posters aren't 'anon', just unregged, sorta like someone who gets banned or deleted?


- Allen wavey
- What Drives You?
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
OK - so it kinda worked but didn't update everything. Let me tweak it a bit.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Allen - alter the first query to be like this:

Code
<br /><br />	SELECT B_AnonName,B_Number<br />	FROM {$config['tbprefix']}Posts<br />	WHERE B_Reged = 'n'<br />	OR B_PosterId = 1<br />


See if that does more.

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Done! 29763 post records updated!



OK, it works... if I try it on the other site with 400k posts it might kill the server, I'll give it a shot late at night


- Allen wavey
- What Drives You?
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
he he
Yeah, it's not exactly "efficient" - but it works.

If the above times out - it shouldn't hurt to simply run it again - as each time it's grabbing anonymous posts and processing them. So if you have to rerun it, it'll just keep picking anonymous posts and looking up the user.


Also - the last little tidbit will be to update post counts (counting up all their posts) so that their profile is correct. I'll make you a separate little script for that. So that after you're done, you can run that and then the user's post counts will be correct.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Allen - try this script (untested) to update everyone's post counts.
Attachments
109302-count.txt (0 Bytes, 127 downloads)

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
sweet, worked real well


- Allen wavey
- What Drives You?
Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
How about a query that will pull all users that have posted in the past X number of days?

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
You want fries and a coke with that

SELECT t2.U_Username
FROM w3t_Posts AS t1
LEFT JOIN w3t_Users AS t2 ON t1.B_PosterId = t2.U_Number
WHERE B_Posted > UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 1
MONTH ) )
GROUP BY t2.U_Username ASC

Change the interval to whatever you want

3 DAY
7 DAY
15 DAY
1 WEEK
3 WEEK
6 WEEK
2 MONTH
6 MONTH
etc etc

Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
w00t! You da man!

Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
I want to write a query that will delete all threads that were either closed or moved over a month ago. Here is what I have come up with:

Code
 <br />DELETE FROM w3t_Posts <br />WHERE B_Last_Post > UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 1 MONTH ) ) <br />AND B_Status = "C" <br />OR B_Status = "M" <br />


Is this all correct?

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
Question.. whatif there is a poll in one of those posts.. won't that mess up the poll tables?

Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
So I guess I'd have to make a script out of it then, so it can look for polls and purge the corresponding tables? But without that, it would just leave orphaned tables though, right? No harm but taking up space, right?

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Yeah, I wouldn't manually delete posts - there are several other things which need adjusting and mismatched post counts to board totals and post totals are exactly what cause that blank page issue you have from time to time.

Study the scripts which expire or delete the threads - and you'll see the stuff that needs to be deleted as well.

Off the top of my head:

- Adjust the post/thread count in the boards table.
- Any users who have those threads as their favorites
- Anybody with post reminders to those threads
- Polls, their options and results attached to those posts
- File attached to those posts

You don't want all that stuff to get out of sync.
There might be more - just thinking out loud.

Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
As Josh suggested study the expire files real well to see everything that transpires.

Joined: Apr 2004
Posts: 3
Lurker
Lurker
Offline
Joined: Apr 2004
Posts: 3
Has anyone ever written a script to automate adding new users or adding a long list of users in one fell swoop?

We've got a bunch of users from a newsgroup and would like to move the entire group to ubb.threads but adding users one by one is drudgery.

Seems like it'd be possible to figure out by analyzing the addusers.php file line by line but just wondering if anyone's tried it.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
You could add the users and give them all the same generic password which they could then change using an INSERT but all the other options that basically get entered also would suffer the same fate as the password. Easier to setup the board and let the user himself register himself.

Joined: Apr 2004
Posts: 3
Lurker
Lurker
Offline
Joined: Apr 2004
Posts: 3
We'd like to keep the board ultra-secure/private so I've deleted the New User button and am being the doorman.

We've already got old user info/password hashes in mysql, and would like to just transfer this old mysql database to ubb.threads.

Seems like there's a lot more involved than just the w3t_users table when creating new users though. If anybody's got any idea of the web of chain mysql reactions that goes into creating a new user, please give a honk.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
Just take a look at Adduser.php its all there

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305

Joined: Apr 2004
Posts: 3
Lurker
Lurker
Offline
Joined: Apr 2004
Posts: 3
Wow, that mod rocks and does most of the job.

Thanks scroungr!


Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Here is one I need help with. I did some major group re-structuring last week, and a few dozen people (maybe more) are now not part of any group, not even the default "Users"

This causes them to get mysql errors when trying to browse the forums, since they aren't in a group so they get weird errors.

I want a query to find these users who do not have a group assigned so I can fix their accounts. And even better, another query to automatically search for someone without a group, and if they don't have one they are assigned to the Users group.

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
Select * from w3t_Users where U_Groups = "" or U_Groups is NULL or U_Groups = "--"

That should grab all users with empty groups. The last one shouldn't be necessary.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
yeah then do an

UPDATE w3t_Users SET U_Groups = "-4-" WHERE U_Groups = "" or U_Groups is NULL or U_Groups = "--"

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
Thx, forgot to write up the update statement.

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Thanks, you guys rock

Joined: Jan 2003
Posts: 338
Enthusiast
Enthusiast
Offline
Joined: Jan 2003
Posts: 338
TOP posters from any forum (here 'hp')

SELECT t1.B_PosterId,t2.U_Username,count(*) AS liczba
FROM w3t_Posts AS t1,w3t_Users AS t2
WHERE t1.B_PosterId = t2.U_Number
AND t1.B_Board = "hp"
GROUP BY t1.B_PosterId
ORDER BY liczba desc

Joined: Apr 2002
Posts: 474
Enthusiast
Enthusiast
Offline
Joined: Apr 2002
Posts: 474
How would I find out what languages my members are using?
so that when I upgrade I know what ones I can dump.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
SELECT DISTINCT U_Language FROM w3t_Users

Last edited by scroungr; 05/27/2004 2:39 PM.
Joined: Apr 2002
Posts: 474
Enthusiast
Enthusiast
Offline
Joined: Apr 2002
Posts: 474
Thanks

Joined: Aug 2002
Posts: 100
Journeyman
Journeyman
Offline
Joined: Aug 2002
Posts: 100
Ok, I have now decided to turn OFF display names, but some users still have display names in the DB...

What would be the query to change everyone's display name to their login names? (So they are both the same)

Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
?
UPDATE w3t_Users
SET U_Username = U_LoginName

Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the literal display name of "U_LoginName" so I wouldn't really recommend trying it.

Joined: Mar 2004
Posts: 16
Newbie
Newbie
Offline
Joined: Mar 2004
Posts: 16
I need a Query to reset everyones name color to the default "Normal" How would this be done?

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
well what is normal ? heh BUT you can try

UPDATE w3t_Users SET U_Color = ''

Joined: Sep 2003
Posts: 803
Coder
Coder
Offline
Joined: Sep 2003
Posts: 803
If you want to make the color of the names the default color you have to type "Normal" into the color section. It will make them the forum default.

So my guess is to do...
UPDATE w3t_Users SET U_Color = 'normal'
(could be worng though. dont run that unless you ask someone)

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
has to be normal? thought it could be empty? they change it? if it has to be normal then yes DrChaos is chaotically correct

Joined: Sep 2003
Posts: 803
Coder
Coder
Offline
Joined: Sep 2003
Posts: 803
I said I could be wrong

I canged a few and just removed the name of the color where edit the user and nothing happend.

then one of my admins said to try to put "normal", because that what all the regular users are set to, and it worked.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
well then you are right now wrong and I am wrong not right so glad to meet ya

charts #258597 10/28/2004 1:42 AM
Joined: Nov 2002
Posts: 554
Code Monkey
Code Monkey
Offline
Joined: Nov 2002
Posts: 554
Can anyone tell me the query to require all users to review the Forum Rules and accept or reject on their next visit to the board?

It's that time

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Using the Force Rules mod?

UPDATE w3t_Users
SET U_Agree = 0

Daine #258599 10/28/2004 5:25 AM
Joined: Nov 2002
Posts: 554
Code Monkey
Code Monkey
Offline
Joined: Nov 2002
Posts: 554
Thanks Josh,I didnt know there was a Mod for that.

Joined: Feb 2004
Posts: 16
Newbie
Newbie
Offline
Joined: Feb 2004
Posts: 16
Anyone get brave enough to try this out?

[] ?
UPDATE w3t_Users
SET U_Username = U_LoginName

Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the literal display name of "U_LoginName" so I wouldn't really recommend trying it. [/]

I have a bunch of users who I need to make their eamil / fake email and user name / display name all match. A SQL call like this would save me a lot of time

P-

Joined: May 2001
Posts: 550
Code Monkey
Code Monkey
Offline
Joined: May 2001
Posts: 550
>UPDATE w3t_Users
>SET U_Username = U_LoginName
>Although I'm not sure if that will pull their U_LoginName, or will actually give everyone the
>literal display name of "U_LoginName" so I wouldn't really recommend trying it.

Test it with one name first.

UPDATE w3t_Users
SET U_Username = U_LoginName
WHERE U_Number= 124124

124124 = your user number

Joined: Feb 2004
Posts: 16
Newbie
Newbie
Offline
Joined: Feb 2004
Posts: 16
Good idea

Joined: Feb 2004
Posts: 16
Newbie
Newbie
Offline
Joined: Feb 2004
Posts: 16
I ran the follwing query through MyPHPAdmin and it worked...

UPDATE w3t_Users
SET `U_Username` = `U_LoginName`

Thanks Anno

P-

Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
Here is the old query by RandyJG that JoshPet used in the Stylesheet Popularity mod. Since we have been able to store queries in the past couple of versions, the mod is no longer needed, but the query could still be helpful...

Code
 <br />SELECT COUNT(*) as total,U_StyleSheet as style <br />FROM w3t_Users <br />GROUP BY U_StyleSheet <br />ORDER BY total DESC <br />

Joined: Aug 2004
Posts: 173
Member
Member
Offline
Joined: Aug 2004
Posts: 173
How do I access a list of all of my users e-mail address? Can this be done with a query?

Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
SELECT U_RegEmail, U_Email, U_Fakeemail FROM w3t_Users;


Nikos
Joined: Aug 2004
Posts: 173
Member
Member
Offline
Joined: Aug 2004
Posts: 173
Worked a treat, thank you

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
Select U_Email from w3t_Users where u_number > 1;

Joined: Nov 2001
Posts: 134
Journeyman
Journeyman
Offline
Joined: Nov 2001
Posts: 134
How about a script to show user ID's that have been created from the same IP address, even if you dont know the IP address?

Daine #258610 01/30/2005 2:44 PM
Joined: Nov 2001
Posts: 134
Journeyman
Journeyman
Offline
Joined: Nov 2001
Posts: 134
[]JoshPet said:
Using the Force Rules mod?

UPDATE w3t_Users
SET U_Agree = 0 [/]

This one no longer seems to work on 6.5.. is there a new way of doing this?

Ta

Joined: Mar 2003
Posts: 159
Member
Member
Joined: Mar 2003
Posts: 159
What would the script be to reset/update all users to no avatar?

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
UPDATE w3t_Users SET U_Picture = ''

Joined: Dec 2002
Posts: 7
Lurker
Lurker
Offline
Joined: Dec 2002
Posts: 7
I am wondering if anyone could help out with a query to basically duplicate a group under another name? I have about 5000 members and we're looking for a simple way to plan one heck of a going away party for one of our members without his knowledge. It would seem that this could be easily done by creating a new group and adding all members in the "users" group to this new group.... and then removing our guest of honor from the new group so he wouldn't have permissions to see our "hidden" party planning forum.

Any ideas? I've played with doing this manually, a couple members at a time, but it is rediculously slow going.

Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
version of threads would be helpful since...most versions can do it already at least 6.4 and 6.5 by just adding a new user group and then selecting everyone in user group and adding them to new group?

234234 #258615 04/15/2005 10:09 AM
Joined: Dec 2002
Posts: 7
Lurker
Lurker
Offline
Joined: Dec 2002
Posts: 7
6.4.1

I've already tried to do this through the AP using the "mass group change" but if I try to do any more then 4 or 5 members at a time it just grinds to a halt.

Joined: Jan 2003
Posts: 263
Member
Member
Joined: Jan 2003
Posts: 263
I need a query to do the following:

List all usernames + email adresses + number of PM's in their inbox from users with more than 2 PM's in their INBOX

How?

Joined: Jan 2003
Posts: 263
Member
Member
Joined: Jan 2003
Posts: 263
Ok, I think I got it, but I'm not 100% if this is correct:


--------------------------------------------------------------------------------

SELECT w3t_Users.U_Email,
w3t_Users.U_LoginName,
w3t_Users.U_Username,
COUNT(w3t_Messages.M_Uid)
FROM w3t_Messages, w3t_Users
GROUP BY w3t_Users.U_Number
HAVING COUNT(w3t_Messages.M_uid) > 2

I tried to run this, but my browser seemed to hang. I cancelled the query after 20 minuten (my whole database is only 40mb).

What am I doing wrong here ?

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3

To delete pm's (like all those welcome pm's that re-appear after upgrading), try these commands:

Code

DELETE FROM `ubbt_PRIVATE_MESSAGE_TOPICS` WHERE `TOPIC_SUBJECT` = 'Welcome'

DELETE FROM `ubbt_PRIVATE_MESSAGE_POSTS` WHERE `POST_BODY` LIKE '%Welcome to our forums%'

Change the topic subject wording to whatever you send to new registered members. smile

There's still the ubbt_PRIVATE_MESSAGE_USERS table - I imagine a script would need to be written to match the pm with the topic id being deleted above and clean that out too.


- Allen wavey
- What Drives You?
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
This is what I do:
Code
 
SELECT R_Rating, R_Rater, U_Username, U_Totalposts, R_What
FROM _forum_Ratings, _forum_Users
WHERE R_Rater = U_Number
AND R_Type = 'u'
AND U_Totalposts < 50
ORDER BY U_Totalposts, R_Rater ASC

I get a list of Users who have less than 50 posts and have done a rating on somebedy.

I also get the name of the user who rated but I don't know how to get the name of the user who WAS rated (R_What is user ID).

Can anyone provide me with solution?


Kind regards,
PaNTerSan
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
Next question is how to update U_Rating, U_Rates, U_RealRating to match values in R_Rating?

Of course without having to write a script but rathrer running a query smile


Kind regards,
PaNTerSan
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 version are you running?


- Allen wavey
- What Drives You?
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
Originally Posted by AllenAyres
What version are you running?
6.5.5

I'll write the script but I would like to learn how to do it via sql if possible smile


Kind regards,
PaNTerSan
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Originally Posted by PaNTerSan
This is what I do:
Code
 
SELECT R_Rating, R_Rater, U_Username, U_Totalposts, R_What
FROM _forum_Ratings, _forum_Users
WHERE R_Rater = U_Number
AND R_Type = 'u'
AND U_Totalposts < 50
ORDER BY U_Totalposts, R_Rater ASC

I get a list of Users who have less than 50 posts and have done a rating on somebedy.

I also get the name of the user who rated but I don't know how to get the name of the user who WAS rated (R_What is user ID).

Can anyone provide me with solution?


You are querying for R_What - that's not giving you the result you are looking for?


- 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
This really isn't quite a mod, but may help those wanting to increase activity on their sites. Currently it's a bit of a jump through the hoops to get emails for watched topics and forums - I've repeatedly had to tell people the couple of things they have to do to get emails.

Here's the mysql commands you put into your SQL Command window to set everyone to receive emails for replies to any topics they are watching:

Code

UPDATE ubbt_USER_PROFILE SET USER_EMAIL_WATCHLISTS = '1'

Code

UPDATE ubbt_WATCH_LISTS SET WATCH_NOTIFY_IMMEDIATE = '1'

and if you also want to set all users to receive email when they get a pm, use this one:

Code

UPDATE ubbt_USER_PROFILE SET USER_NOTIFY_ON_PM = 'yes'

And lastly, if you want all users to receive emails from admins, then use this one:

Code

UPDATE ubbt_USER_PROFILE SET USER_ACCEPT_ADMIN_EMAILS = 'yes'

note: the last one might get a few complaints since I believe it specifically is set by the user to opt out, I may be wrong - it's been a while since I was a newbie wink


- Allen wavey
- What Drives You?
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
In addition to those commands from Allen, I would strongly recommend editing all your emails that go out - at the moment they are very weak, and do not let the user know how to unsubscribe from the email that they have just received.

For example my notification of a PM says...

Quote
You have received a private message at Fans Focus. You can go to http://www.fansfocus.com/forums/ubbthreads.php to view it.

You can turn off these notifications from http://www.fansfocus.com/forums/ubbthreads.php/ubb/editdisplay on the forums.

http://www.fansfocus.com

--------------------

Nothing elaborate - but it gets around anyone accusing us of spamming them.

And all my emails have the subject starting...

FansFocus.Com:



Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Page 1 of 3 1 2 3

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)