Previous Thread
Next Thread
Print Thread
Rate Thread
Page 2 of 3 1 2 3
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!

Sponsored Links
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.

Sponsored Links
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

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

Page 2 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
badfrog
badfrog
somewhere on the coast of Maine
Posts: 94
Joined: March 2007
Forum Statistics
Forums63
Topics37,570
Posts293,918
Members13,848
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,830
Greg Hard 4,625
Top Posters(30 Days)
Gizmo 1
Top Likes Received
isaac 81
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2023 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)