Lost: gsf's "A Ternary Monoid for Sudoku Coloring"

Advanced methods and approaches for solving Sudoku puzzles

re: orphaned posts

Postby Pat » Mon Aug 20, 2007 11:54 am

Ruud wrote:In the phpbb database, all anonymous posts have `poster_id`= -1. There is a predefined "Guest" with this ID in the user table.

When you are posting anonymously, there's a "username" field in which you can enter your name. This name is saved in the `posts` table in the `poster_username` field.


with Ruud's explanation,
the light finally dawns. (sorry, i'm slow.)

  • posts when not logged-in
    were immediately marked poster_id=-1
  • for posts of properly-cancelled users (invalid e-mail),
    the database (Topics and Posts) was properly updated to poster_id=-1
  • but when Nick67 and rep'nA ( and possibly others ) were improperly cancelled, Topics and Posts were not updated to poster_id=-1, thus the corrupt database -- the join will not retrieve those topics or posts for which poster_id is not found in the table of users -- this explains the strange phenomena observed -- and since the original poster_id is still recorded, it also allows us to recover these posts

do i still remember some SQL ?
first find any orphaned Topics (later do same for Posts)
    select topic_id , poster_id from Topics
    where poster_id not in (select user_id from Users)
User avatar
Pat
 
Posts: 3438
Joined: 18 July 2005

Postby Ruud » Sat Aug 25, 2007 12:47 pm

Nick67's 113 posts have been restored.
Ruud
 
Posts: 664
Joined: 28 October 2005

re: recovering Nick67

Postby Pat » Sun Aug 26, 2007 9:45 am

Ruud wrote:Nick67's 113 posts have been restored.

beautiful
    yet somehow his Total Posts shows as 0
User avatar
Pat
 
Posts: 3438
Joined: 18 July 2005

Previous

Return to Advanced solving techniques