| Author |
 Topic  |
|
|
BWJM
Junior Member
Canada
193 Posts |
 Posted - 16 September 2002 : 15:24:43
|
OK, I am going to willingly admit that I've cheated a bit and deleted stuff from my database directly via SQL commands instead of clicking through the links to delete a member, etc.
Now, I'm pretty sure that there are "orphaned" topics, replies, etc throughout the database that should be cleaned up.
Is there a way to traverse the database and remove any replies with non-existant topic_ids or authors, topics with bad authors, etc?
An optional thing is to manually recalculate each users' post counts. I have a feeling that some Admins on my site may have "doctored" some users post counts and some may be overestimated due to deleted topics.
Thanks!!! |
 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 16 September 2002 : 15:41:38
|
There is a reason that Delete a Member doesn't actually delete the record from the MEMBERS table, and it sounds like you've discovered that reason :)
You could create some outer joins from the affected tables back to the Members table & see what records come up blank (i.e., deleted member). Then you will have to decide what you want to do with the topics or replies that have no associated member, you may not want to just delete the record unless you don't care about the information in the posts.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
 |
|
|
BWJM
Junior Member
Canada
193 Posts |
Posted - 16 September 2002 : 16:11:05
|
Could you help me out there? I'm OK with basic SELECT, UPDATE, INSERT, DELETE, etc, but I never got the hang of the INNER or OUTER JOIN stuff yet.
At this point, I'm not at all concerned with the content of the orphaned posts or topics. Let's try and get a SELECT to view everything that is orphaned and then from there, we can do a delete.
Thanks |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 September 2002 : 19:46:41
|
Here goes a query to find orphaned topics:
SELECT FORUM_TOPICS.TOPIC_ID FROM FORUM_TOPICS LEFT JOIN FORUM_MEMBERS ON FORUM_TOPICS.T_AUTHOR=FORUM_MEMBERS.MEMBER_ID WHERE FORUM_MEMBERS.MEMBER_ID Is Null
A similar thing could be done with the replies table... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 16 September 2002 : 20:01:31
|
Thanks, ruirib.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
BWJM
Junior Member
Canada
193 Posts |
Posted - 16 September 2002 : 23:59:51
|
| Thanks guys! |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
.