vb.org Archive - Userfield messed up ?? heres the fix

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - Userfield messed up ?? heres the fix (https://vborg.vbsupport.ru/showthread.php?t=46404)

Userfield messed up ?? heres the fix

AFter a lot of pruing etc i found that my USER table had 33799 rows but my userfield table ahd 134,786 rows. Now we had deleted 3 years worth of zero posters to get these figures - but what about the bloated userfield db ?

i'm sure VB should have prined this up auto - well if it does it didnt in this case - and i doubt it could do it faster than this script.

The script checks that the userid in userfield table exists in user table. If it doesnt it deletes it.

After 30 seconds I have 33799 User and 33799 userfield :)

heres the code - maybe of some use to someone ?

PHP Code:


		
			
<?php



$link = mysql_connect("dbserver", "database", "password");

mysql_select_db("dforums");



set_time_limit(300000000);



$foundresult = mysql_query("select * from userfield order by userid asc");

$foundnum = mysql_num_rows($foundresult);



echo("Deleting obsolete UserID entries from userfield...<br>\r\n");



for ($i=0;$i<$foundnum;$i++)

{

    $row = mysql_fetch_array($foundresult);

    $huntId = $row["userid"];

    

    // now lookup user

    $huntresult = mysql_query("select * from user where userid='$huntId'");

    $huntnum = mysql_num_rows($huntresult);

    

    if ($huntnum == 0)

    {

        echo("Deleting: $huntId<br>\r\n");

        mysql_query("delete from userfield where userid='$huntId'");

    }

}



mysql_close();

?>

hmm, looks good so
(i have deletet out your dbname and password btw)

just, a question: are you sure you haven't messed up your admin/user.php

normally the entries in userfield are deleted:

PHP Code:


		
			
  $DB_site->query("DELETE FROM user WHERE userid='$userid'");

  $DB_site->query("DELETE FROM userfield WHERE userid='$userid'");

You may need to repair your userfield table.

I had the opposite problem. I had missing userfield entries that needed to be added and it was messing up the admin "search for user" form. I would get no results when searching for a member who didn't have a userfield entry. I made a couple small changes to your script and it worked great. Thanks

PHP Code:


		
			
<?php



$link = mysql_connect("servername", "dbusername", "dbpassword");

mysql_select_db("dbname");



set_time_limit(300000000);



$foundresult = mysql_query("select * from user order by userid asc");

$foundnum = mysql_num_rows($foundresult);



echo("Adding missing UserID entries to userfield...<br>\r\n");



for ($i=0;$i<$foundnum;$i++)

{

    $row = mysql_fetch_array($foundresult);

    $huntId = $row["userid"];

    

    // now lookup user

    $huntresult = mysql_query("select * from userfield where userid='$huntId'");

    $huntnum = mysql_num_rows($huntresult);

    

    if ($huntnum == 0)

    {

        echo("Adding: $huntId<br>\r\n");

        mysql_query("INSERT INTO userfield (userid) VALUES ($huntId)");

    }

}



echo("Finshed<br>\r\n");





mysql_close();

?>

it checks the user table and then looks for a userfield entry, if one isn't found it makes one.

make sure to change servername, dbusername, dbpassword, & dbname to your settings.

X vBulletin 3.8.12 by vBS Debug 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 (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)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
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01039 seconds Memory Usage 1,738KB 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 (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)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
Messages: