[]Scream said:
As for trying to track down the problem with #7 and #8. On import are you setting B_Approved to 'yes' on all imported posts? The Bo_Total and Bo_Threads fields in the w3t_Boards table should only match the total # of threads/posts that have the B_Approved field set to 'yes'. If any threads/posts do not have that field set properly you may find some inconsistencies. [/]
Thanks for the update (I'm the author of the original queries; Jim is working with me on the site and posted them here).
FYI, all, I've fixed those queries to be:
SELECT Bo_Keyword, Bo_Threads, COUNT(*) AS ActualThreads FROM w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE B_Number = B_Main AND B_Approved='yes' GROUP BY Bo_Keyword HAVING ActualThreads != Bo_Threads
and
SELECT Bo_Keyword, Bo_Total, COUNT(*) AS ActualPosts FROM w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE B_Approved='yes' GROUP BY Bo_Keyword HAVING Bo_Total != ActualPosts
Unfortunately, this hasn't gotten rid of my inconsistencies: we're still trying to track down what action creates them. They show up on boards other than imported boards, so I think it's not an import issue.
Also, here's another one, which finds posts that are on a different board from their parent post or their main thread post:
SELECT t1.B_Number, t1.B_Parent, t1.B_Main, t1.B_Board, t2.B_Board as ParentBoard, t3.B_Board as ThreadBoard FROM w3t_Posts AS t1 JOIN w3t_Posts AS t2 ON t1.B_Parent = t2.B_Number JOIN w3t_Posts AS t3 ON t1.B_Main = t3.B_Number WHERE t1.B_Parent != 0 AND (t1.B_Board != t2.B_Board OR t1.B_Board != t3.B_Board)
I'm also working on a script which will find, display, and repair all of these problems. I'll post a reference to it here when I'm done, if anyone's interested.
