Utility to update pmtotal and pmunread in user table

[Quarterbore]

About this Script:

This script will update the PM Count and Undead PM counts in the user table.

Why Did I write this:

Well, I wrote it as I could not find a built in function in vBulletin to rebuild these values! I had my PM table become corrupted during an install process and while it was easy to restore the PM tables (there are three of them) I had issues as the USER table did not accurately represent the values in the PM table.

I tried searching for a solution and I learned that others have had this issue with board imports, pruning old PMs, or as in my case when it was necessary to recover data from a backup.

What does the script do:

This script updates the USER table to show the correct count of TOTAL PMs and UNREAD PMs for all users.

Why is this so "unrefined"?

This is intended as a utility. I included a permissions check to verify the person running the script is an Admin. I would suggest that this is not a script that you would want sitting on your server all the time but instead is being shared as a tool for cases where someone needs it!

Credits and Revisions

9-Feb-2007 Version 2

thincom2000 did a major rewrite of the script to go from three SQL queries down to one. The script is much faster now and he is listed as Co-author! THANKS!

9-Feb-2007 Version 3

Fixed this bug: https://vborg.vbsupport.ru/showpost.php?p=1178704&postcount=11

[Surviver]

Thanks, but this Script isn't good for the Performance. Up to 2 Queries per User. For bigger forums this could be a problem ;)

Thanks for Sharing ;)

Greeting Surviver

[Quarterbore]

Yea, I assume you would only run this if you had a major meltdown...

I tried to get the join syntax right but odds are i would only need to run this script once so it was not worth the time for me at this point... If vBulletin was to build this function in to vBulletin, then it would be worth their time to optomize this!

EDIT - Funny, I get one TERRIBLE vote... This is not posted for something people will install and run every day... this is posted here in case you need to import users, merge forums, or your data gets corrupted and you need to FIX your database so your PM counts and count of unread messages is correct...

If someone needed this I bet they would be glad I bothered to share the code.... :mad:

[da420]

Thanks, I could have used this a couple weeks ago after merging a forum with my current one. But, the problem somehow fixed itself... :P

Sorry to hear that someone gave you a terrible rating.

[kenji4861]

Thank you. I had a PM spammer on my site and I manually went into the DB to delete their PMs... so the PM counts were screwed up.

Thank you thank you!

[thincom2000]

Hm... I could join those first three queries for you if you so desire.

In fact I already did. Your new countupdate.php should be the following:
<?php

// ####################### SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);

// ############# REQUIRE BACK-END #######################

require_once('./global.php');


// ############# UPDATE PM COUNTS IN USER TABLE #######################

if($vbulletin->userinfo['usergroupid'] != '6'){
exit();
}


// Get all users PMs
$pmdata = $db->query_first("
SELECT user.userid, user.pmtotal, user.pmunread,
pm.pmunread AS pmsunread, pm.pmid
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "pm AS pm ON(pm.userid = user.userid)
WHERE user.userid != 0
ORDER BY user.userid ASC
");

while ($pms = $db->fetch_array($pmdata))
{
$lastuser = ($lastuser == $pms['userid']) ? true : false;
$pmtotal = $lastuser ? $pmtotal : 0;
$pmunread = $lastuser ? $pmunread : 0;

if ($pms['pmid'])
{
$pmtotal++;

if ($pms['pmsunread'])
{
$pmunread++;
}
}

$pmusers["$pms[userid]"] = array(
'true_total' => $pmtotal,
'user_total' => $pms['pmtotal'],
'true_unread' => $pmunread,
'user_unread' => $pms['pmunread']
);
}
$db->free_result($pmdata);
unset($pms, $pmtotal, $pmunread, $lastuser);

foreach ($pmusers AS $pmuserid => $pminfo)
{
if (($pminfo['true_total'] != $pminfo['user_total']) OR ($pminfo['true_unread'] != $pminfo['user_unread']))
{
$db->query_write("
UPDATE " . TABLE_PREFIX . "user
SET pmtotal = $pminfo[true_total],
pmunread = $pminfo[true_unread]
WHERE userid = $pmuserid
");
echo "USERID $pmuserid was updated!<br />";
}
}
unset($pmuserid, $pminfo, $pmusers);

echo 'All Done!';
?>

[Quarterbore]

Hm... I could join those first three queries for you if you so desire.

In fact I already did. Your new countupdate.php should be the following:
<?php

// ####################### SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);

// ############# REQUIRE BACK-END #######################

require_once('./global.php');


// ############# UPDATE PM COUNTS IN USER TABLE #######################

if($vbulletin->userinfo['usergroupid'] != '6'){
exit();
}


// Get all users PMs
$pmdata = $db->query_first("
SELECT user.userid, user.pmtotal, user.pmunread,
pm.pmunread AS pmsunread, pm.pmid
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "pm AS pm ON(pm.userid = user.userid)
WHERE user.userid != 0
ORDER BY user.userid ASC
");

while ($pms = $db->fetch_array($pmdata))
{
$lastuser = ($lastuser == $pms['userid']) ? true : false;
$pmtotal = $lastuser ? $pmtotal : 0;
$pmunread = $lastuser ? $pmunread : 0;

if ($pms['pmid'])
{
$pmtotal++;

if ($pms['pmsunread'])
{
$pmunread++;
}
}

$pmusers["$pms[userid]"] = array(
'true_total' => $pmtotal,
'user_total' => $pms['pmtotal'],
'true_unread' => $pmunread,
'user_unread' => $pms['pmunread']
);
}
$db->free_result($pmdata);
unset($pms, $pmtotal, $pmunread, $lastuser);

foreach ($pmusers AS $pmuserid => $pminfo)
{
if (($pminfo['true_total'] != $pminfo['user_total']) OR ($pminfo['true_unread'] != $pminfo['user_unread']))
{
$db->query_write("
UPDATE " . TABLE_PREFIX . "user
SET pmtotal = $pminfo[true_total],
pmunread = $pminfo[true_unread]
WHERE userid = $pmuserid
");
echo "USERID $pmuserid was updated!<br />";
}
}
unset($pmuserid, $pminfo, $pmusers);

echo 'All Done!';
?>


Well, not quite...

I tested and this generates an error...

Database error in vBulletin 3.6.4:

Invalid SQL:

SELECT user.userid, user.pmtotal, user.pmunread,
pm.pmunread AS pmsunread, pm.pmid
FROM user AS user
LEFT JOIN pm AS pm ON(pm.userid = user.userid)
WHERE user.userid != 0
ORDER BY user.userid ASC;

MySQL Error : Unknown column 'pm.pmunread' in 'field list'
Error Number : 1054
Date : Thursday, February 8th 2007 @ 09:11:45 PM
Script :htt p://www.XXXXXXX.com/forums/countupdate.php
Referrer :
IP Address : 72.94.246.202
Username : Quarterbore
Classname : vb_database

I am trying to find the issue byt MYSQL is not my strong point.

EDIT - This is why I did the simple code I did... even with my MYSQL manuals I can not fix this... I need some sleep but thanks for the kindness... I just had a rough 24-hrs of bringing back a crashed forum and too little sleep...

[thincom2000]

Ah, I should have checked the PM table before writing that :o . It's not pmunread but messageread. That will require a slight code modification, but you shouldn't get a database error with this:

<?php

// ####################### SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);

// ############# REQUIRE BACK-END #######################

require_once('./global.php');


// ############# UPDATE PM COUNTS IN USER TABLE #######################

if($vbulletin->userinfo['usergroupid'] != '6'){
exit();
}


// Get all users PMs
$pmdata = $db->query_first("
SELECT user.userid, user.pmtotal, user.pmunread,
pm.messageread, pm.pmid
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "pm AS pm ON(pm.userid = user.userid)
WHERE user.userid != 0
ORDER BY user.userid ASC
");

while ($pms = $db->fetch_array($pmdata))
{
$lastuser = ($lastuser == $pms['userid']) ? true : false;
$pmtotal = $lastuser ? $pmtotal : 0;
$pmunread = $lastuser ? $pmunread : 0;

if ($pms['pmid'])
{
$pmtotal++;

if (!$pms['messageread'])
{
$pmunread++;
}
}

$pmusers["$pms[userid]"] = array(
'true_total' => $pmtotal,
'user_total' => $pms['pmtotal'],
'true_unread' => $pmunread,
'user_unread' => $pms['pmunread']
);
}
$db->free_result($pmdata);
unset($pms, $pmtotal, $pmunread, $lastuser);

foreach ($pmusers AS $pmuserid => $pminfo)
{
if (($pminfo['true_total'] != $pminfo['user_total']) OR ($pminfo['true_unread'] != $pminfo['user_unread']))
{
$db->query_write("
UPDATE " . TABLE_PREFIX . "user
SET pmtotal = $pminfo[true_total],
pmunread = $pminfo[true_unread]
WHERE userid = $pmuserid
");
echo "USERID $pmuserid was updated!<br />";
}
}
unset($pmuserid, $pminfo, $pmusers);

echo 'All Done!';
?>

The MySQL manuals didn't help me at all when I was trying to learn joins either. Eventually I just had to figure out why what vBulletin did worked.

[Quarterbore]

Looks like you still had one error in the query (you have query-First) but with that changed the script works great and much faster then the code I had uploaded...

I am adding you as a co-author...

Thanks for the advise and improvement!

I am uploading the updated version now!

[kenji4861]

The new version messed up the count total. I'm getting pmtotal of 1, and 0 unread.. for all users.

[kenji4861]

Got it... $lastuser is being overwritten with a boolean when it should have the lastuser's id.

Line 31 should have this
$lastuser = $pms['userid'];

[Quarterbore]

Got it... $lastuser is being overwritten with a boolean when it should have the lastuser's id.

Line 31 should have this
$lastuser = $pms['userid'];


You are correct... Zip is updated and I checked it on a live board this time with real PM users ;) My Dev board has me, myself , and I and while there are three accounts we don't tend to PM each other much :p

[thincom2000]

Got it... $lastuser is being overwritten with a boolean when it should have the lastuser's id.

Haha, sorry I threw that code together in a bit of a rush (just copy and pasted most of your old code which is how it still had query_first and not query_read), and forgot to add the other line setting $lastuser to $pms['userid']. Glad you all figured it out.

[Eagle Creek]

What's the whole problem we are talking about? What PM counter do u mean exactly, and how could it have a negative effect on your forums?

[Quarterbore]

It is the PM count that shows in the header or in your profile... It could say you have 10 PMs when you have 3000 (or the opposite) and your PM limit will be based on that count! You can also have issues with the system sayingyou ALWAYS have 2-unread PMs... but they will NEVER CLEAR as you need to reed those unread PMs before the count will reset. The problem is, you don't have any unread PMs...

If you ever have the problem, you will know it as your members will complain!

[Eagle Creek]

I have never experienced problems with that to be honest..

[Tralala]

I could have used this awhile back, some users had something like "62781287 unread messages." Only way to fix this (wrong) number was to empty their PM mailbox.

Thanks for sharing, Quarterbore!

[Tralala]

Perhaps to quell concerns, this utility would be better served as a menu item available in the AdminCP, available under "Maintenance."

[Quarterbore]

True... I actually have been trying to get this considered for an addition in vBulletin itself (they can recode as they would like) and I have posted this over at vBulletin.com as a suggestion for inclusion. We will see...

[Eagle Creek]

True... I actually have been trying to get this considered for an addition in vBulletin itself (they can recode as they would like) and I have posted this over at vBulletin.com as a suggestion for inclusion. We will see...
Did u make a bugreport of it?

[Tralala]

Did u make a bugreport of it?


Not a bad idea; technically this utility is only necessary because occasionally the pmtotal and pmunread numbers can go crazy. That's the bug. This utility provides a "fix."

[Netjak]

Hello,

I have this issue on my forums. I recently updated to the latest version Vb and then all my users PM totals went wonky (is that a word?). Anyways my question is, how do i run this script?

Do I upload it to a specific folder on my server then go to that url? Do i import it with VB product manager? I'm clueless, please help...

Cheers

[Eagle Creek]

It's a PHP file. So U have to run it, by opening it, or by using WGET or something. I don't know if vB task manager can run .PHP files, but im pretty sure of it.

[Netjak]

It's a PHP file. So U have to run it, by opening it, or by using WGET or something. I don't know if vB task manager can run .PHP files, but im pretty sure of it.

Ok so how would I specifically go about it? Cause I uploaded the script to my forums main directory and then went to that URL and just got a error page saying nothing important.

[Alien]

Downloaded it, ran it and get:

Parse error: syntax error, unexpected T_STRING in /thepathhere/forums/admincp/countupdate.php on line 13