phpBB2 restore deleted member [resolved]

All web design discussion, including Ultimate Quiz MOD support.

Moderator: CricketMX Forum Moderators

Post Reply
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

I'm trying to assign a "Guest" 'poster_id' back to that of an originally deleted member (long story...). So far I've done an initial query in MyAdmin (I'm trying this out on a dummy deleted account at my test site):

SELECT * FROM phpbb_posts WHERE poster_id = -1 AND post_username = '<name>'

The query correctly shows all posts where the poster_id is -1 with matching username.

What I need to do now, is change the poster_id to the original user_id number of 10 (of which the deleted account has been recreated and the user_id changed). How do I do that?

I'm also aware that the same would need doing for topic_poster. The only thing I'm unsure of is whether the post count would need updating as well and if the SQL would need doing the same time the above query is done?

When I think of it, this could be an extremely handy MOD for anyone unfortunate to accidentally delete a members account, especially one originally consisting of many, many posts. I am aware that the alternative is to think of it as bad luck and to create another account but it can't be that difficult to "undelete"... is it?

Note: My assistance required is for phpbb2. ;)


EDIT: I've come across the following but it doesn't work when run because it keeps returning a parse error. I've tried to fathom it out but with no joy. :(

Code: Select all

<?php
    $host=”localhost”;
    $user=”********”;
    $password=”********”;
    $dbname=”phpb2”;
    $link=mysql_connect ($host, $user, $password);

    $nr_posts = 0;

    $query = “SELECT * FROM phpbb_posts WHERE poster_id = -1 AND post_username = '<someone>'";

    $result=mysql_db_query($dbname, $query, $link);

    while ($Row = mysql_fetch_array($result))
  {
    $queryz = “SELECT * FROM phpbb_topics WHERE topic_first_post_id = $Row[post_id] AND topic_poster = -1";
    $resultz=mysql_db_query ($dbname, $queryz, $link);
    $num_rows = mysql_num_rows($resultz);

    if ($num_rows)
 {
    print $Row[post_id] . ” - “;
    $tRow = mysql_fetch_array($resultz);
    $queryz = “UPDATE phpbb_topics SET topic_poster=10 WHERE topic_id = $tRow[topic_id]” ;
    $resultz=mysql_db_query ($dbname, $queryz, $link);
    }

    $queryz = “UPDATE phpbb_posts SET poster_id=10 WHERE post_id = $Row[post_id]” ;
    $resultz=mysql_db_query ($dbname, $queryz, $link);

    $nr_posts = $nr_posts + 1;

    }

    $queryz = “UPDATE phpbb_users SET user_posts = $nr_posts WHERE user_id = 10" ;
    $resultz=mysql_db_query ($dbname, $queryz, $link);

    mysql_close ($link);
?>
Last edited by quahappy on Tue Feb 17, 2009 4:11 pm, edited 1 time in total.
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

I think this might work, I can't test it though. Make sure to put your database connection at the top, and replace the $users_name, $topic_poster_id, etc if you have to.

And be sure to test it on your testing board first, and if you can make sure there are some other posts by guests on your testing board (incase for some reason it changes details for all guests, not just former member-cum-guest that you deleted). And take the appropriate backups if you try it on your main board :)

Code: Select all

<?php
// Database connection here

$users_name = '<someone>';

$sql = "SELECT post_id FROM phpbb_posts WHERE poster_id = -1 AND post_username = '$users_name'";
$result = mysql_query($sql);

$posts_number = 0;
$topic_poster_id = 10;

    while( $row = mysql_fetch_array($result) )
    {
    mysql_query("UPDATE phpbb_topics SET topic_poster = $topic_poster_id WHERE topic_first_post_id = " . $row['post_id'] . " AND topic_poster = -1"); // update topics
    
    $posts_number++; // Using X++ is the same as writing X = (X+1)
    }

mysql_query("UPDATE phpbb_posts SET poster_id = $topic_poster_id WHERE poster_id = -1 AND post_username = '$users_name'"); // update posts
mysql_query("UPDATE phpbb_users SET user_posts = $posts_number WHERE poster_id = $topic_poster_id"); // update users posts

echo '-- End script --';
?>
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Thank you battye for replying! :D

(All of the below done at my test site)

I adjusted the data connection section as what I had wouldn't work so the following is what I did (along with your code):

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
    $users_name = 'membername';
    $sql = "SELECT post_id FROM phpbb_posts WHERE poster_id = -1 AND post_username = '$users_name'";
    $result = mysql_query($sql);

    $posts_number = 0;
    $topic_poster_id = 10;

        while( $row = mysql_fetch_array($result) )
        {
        mysql_query("UPDATE phpbb_topics SET topic_poster = $topic_poster_id WHERE topic_first_post_id = " . $row['post_id'] . " AND topic_poster = -1"); // update topics
        
        $posts_number + 1; // Using X++ is the same as writing X = (X+1)1
        }

    mysql_query("UPDATE phpbb_posts SET poster_id = $topic_poster_id WHERE poster_id = -1 AND post_username = '$users_name'"); // update posts
    mysql_query("UPDATE phpbb_users SET user_posts = $posts_number WHERE poster_id = $topic_poster_id"); // update users posts

    echo '-- End script --';
    ?>
It works! Two little probs though:

1. The total post count is still zero for user_id = 10, but this was easily corrected by doing a search for ALL posts by the member with user_id = 10 which returned:

Search found 13 matches

... which I changed user_posts to 13 (that of user_id: 10) via MyAdmin. ;)


2. For some reason one of the topics shown within a forum is showing the author as "Guest" even though the first post of the topic is by another active user (me!). But other topics started by user_id: 10 correctly shows the username. I will have to do another tester again to check if it's something I may had done within that thread before I contemplate doing this on my live forum (of which backups will be made first!).

Thanks once again battye - really, really appreciate your help. :D
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

If the user only posted 13 times it might be quicker to do it manually in phpMyAdmin, probably less risk involved?

I can't remember off the top of my head the phpBB2 table structure, but in phpbb_topics is there any field for a raw username? Also, I have a feeling in phpbb_posts_text there might be a username field as well, are you able to check that?
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

battye wrote:If the user only posted 13 times it might be quicker to do it manually in phpMyAdmin, probably less risk involved?
I made up 13 posts for this "dummy" member (and deleted it after) at my test site. On my live forum, we are talking about c1000 posts for deleted member. ;)
battye wrote:I can't remember off the top of my head the phpBB2 table structure, but in phpbb_topics is there any field for a raw username? Also, I have a feeling in phpbb_posts_text there might be a username field as well, are you able to check that?
No raw username field in phpbb_posts_text. In phpbb_topics there are the following:

topic_id
forum_id
topic_title
topic_poster
topic_time
topic_views
topic_replies
topic_status
topic_vote
topic_type
topic_first_post_id
topic_last_post_id
topic_moved_id

And in phpbb_posts_text:

post_id
bbcode_uid
post_subject
post_text

It's the post count that I can't see why it hasn't incremented. Like I said in previous post, it's easy enough to alter this figure via MyAdmin.
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

For that post that has a topic poster of Guest and you actually wrote the first post (in phpMyAdmin):

What is the "topic_poster" value set to for that topic? And in phpbb_posts what is the poster_id set to for that first post? And what are the respective topic_id and post_id values?
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Ahhh damn. I had already altered the poster_id of the first post of the topic that showed "Guest" (on index page) to 2 (my user_id) on the test forum before I read your post. Sorry. :(

Just to double-check everything though, and ensure a topic author doesn't show as a Guest again, I'm going to repeat what I did - create two dummy account, make loads of posts, delete the users, then recreate one of the deleted users (remembering the user_id), run the script, then check everything. Will let you know if anything shows up. :D
If you don't ask...
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Update:

Did another test. All worked perfectly.

Have fully restored deleted member at live forum by doing the following:

1. Create new member (using original username)
2. MyAdmin - changed user_id back to deleted member
3. Ran restore member script (that you kindly did for me)
4. Searched within forum for all posts made be reinstated member to obtain post count and changed user_posts to correct value (within MyAdmin).
5. Checked whole forum - all correct.

Cheers battye! :D

Apart from the issue of user_post not updating, which was easily rectified (as per number 4 above), the originally deleted member is now fully reinstated.
If you don't ask...
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Thread renamed for easier reference. :D
If you don't ask...
Post Reply