Update guest-posted messages to correspond with reg'd users

[bigmattyh]

Hey everyone,

Let's say you have a user who posts a number of messages on your board as a guest. We'll call him "Bill." Then, let's say Bill registers as a user! But now his official post count is a big fat zero -- and in truth, Bill posted over 50 messages before he registered! What's a webmaster to do?

Well, fear not. I, Matt Howell, have written you a script that will fix this conundrum. This handy little script searches through all your old guest-posts to see if any were written by someone whose name is registered. So, all the old guest-posts under the name of "Bill" would be displayed just like any other of Bill's posts -- and his post count gets updated in the process, too.

Upload this to your admin directory, and name it whatever you like. It requires no additional modifications, and it works off your already existing config.php for db info.

PHP Code:

<?php

include "config.php";

$GLOBALS["link"] = mysql_connect($servername, $dbusername, $dbpassword)
  or printMySQLError();

$load_db = mysql_select_db($dbname)
  or printMySQLError();

$query = "SELECT username, userid FROM user";
$result = mysql_query($query)
  or printMySQLError();

while ($row = mysql_fetch_array($result)) {
  $username = $row["username"];
  $userid = $row["userid"];

  $query = sprintf("UPDATE post SET userid=%d WHERE username = \"%s\"  AND userid=0", $userid, $username);
  mysql_query($query)
    or printMySQLError();

  $query = sprintf("SELECT COUNT(*) FROM post WHERE userid=%d", $userid);
  $countresult = mysql_query($query)
    or printMySQLError();
 
  $count = mysql_fetch_array($countresult);
  $numposts = $count["COUNT(*)"];

  $query = sprintf("UPDATE user SET posts=%d WHERE userid=%d", $numposts, $userid);
  mysql_query($query)
    or printMySQLError();

  echo ("User ".$userid." completed... \n");
}

echo ("All done!");

function printMySQLError()
{
  $link = $GLOBALS["link"];
  printf("<br> error:%d %s <br>\n", mysql_errno($link), mysql_error($link));
  print ("Please reload--the database server was temporarily down!<br>\n");
}

?>

And all you hardcore PHP-peeps out there -- please don't get on to me for the non-OOP format. I'm just learning how to do OOP now. If you'd like, you can send me examples of how to do DB queries in OOP, and I'll be happy to learn!

bigmattyh ,

Thanks! That works like a dream! The only thing it doesn't do is update the user titles to reflect the post count, but that's easily done in the Admin CP anyway.