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!
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.
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?
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.
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
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':
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.
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.
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.
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
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?
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.
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.
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.
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.
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
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.