vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vBulletin 3.6 Add-ons (https://vborg.vbsupport.ru/forumdisplay.php?f=194)
-   -   Administrative and Maintenance Tools - Utility to update pmtotal and pmunread in user table (https://vborg.vbsupport.ru/showthread.php?t=138827)

Quarterbore 02-07-2007 10:00 PM
Utility to update pmtotal and pmunread in user table
 
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 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 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.... :mad:

da420 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 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 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 02-09-2007 01:14 AM
Quote:
Originally Posted by thincom2000 (Post 1177965)
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 02-09-2007 02:11 AM
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 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 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 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.

kenji4861 02-09-2007 09:24 PM
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 02-10-2007 01:28 AM
Quote:
Originally Posted by kenji4861 (Post 1178704)
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 02-10-2007 02:05 AM
Quote:
Originally Posted by kenji4861 (Post 1178704)
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 02-16-2007 01:36 AM
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 02-17-2007 10:38 PM
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 02-19-2007 04:46 AM
I have never experienced problems with that to be honest..

Tralala 02-19-2007 05:07 AM
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 02-19-2007 05:09 AM
Perhaps to quell concerns, this utility would be better served as a menu item available in the AdminCP, available under "Maintenance."

Quarterbore 02-19-2007 01:36 PM
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 02-19-2007 01:59 PM
Quote:
Originally Posted by Quarterbore (Post 1186002)
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 02-19-2007 02:38 PM
Quote:
Originally Posted by Eagle Creek (Post 1186024)
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 02-21-2007 11:07 PM
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 02-21-2007 11:28 PM
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 02-21-2007 11:38 PM
Quote:
Originally Posted by Eagle Creek (Post 1187943)
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 02-27-2007 12:06 AM
Downloaded it, ran it and get:

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


All times are GMT. The time now is 03:32 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01202 seconds
  • Memory Usage 1,861KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (3)bbcode_php_printable
  • (6)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (25)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.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/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.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
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete