The Arcive of Official vBulletin Modifications Site.

Utility to update pmtotal and pmunread in user table · **Released**: 02-07-2007

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....4&postcount=11

Surviver · #2 02-08-2007, 06:17 PM

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 · #3 02-08-2007, 06:26 PM

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....

da420 · #4 02-08-2007, 11:45 PM

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 · #5 02-08-2007, 11:59 PM

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 · #6 02-09-2007, 12:19 AM

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 Code:


			
<?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 · #7 02-09-2007, 01:14 AM

Quote:

Originally Posted by 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 Code:


			
<?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 · #8 02-09-2007, 02:11 AM

Ah, I should have checked the PM table before writing that

. It's not pmunread but messageread. That will require a slight code modification, but you shouldn't get a database error with this:

PHP Code:


			
<?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 · #9 02-09-2007, 01:23 PM

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 · #10 02-09-2007, 09:16 PM

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.05479 seconds Memory Usage 2,365KB Queries Executed 24 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (3)bbcode_php (1)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)modsystem_post (1)navbar (6)navbar_link (120)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (9)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!