Miscellaneous Hacks - DevTracker : Search by usergroup

DevTracker : Search by usergroup

Description:

Allows you to search for threads containing posts by multiple user ids a.k.a DevTracker

This hack requires you to EDIT a base vbulletin php source file
This hack is in BETA and I could use some feedback please

A good friend (wish i could plug your site Jon but I'd get a slap) mentioned this requirement in passing, and checking vb.com and vb.org yielded no existing hack but plenty of requirements for people to have this on their forum.

Installation

1) Create a new user on your forum, (eg devtracker)

2) Make a note of all the userid's you want to be linked to the tracker, yes I know the title of the hack says usergroups this was just to help search terms (and may come later) (eg 1,5,10,43)

3) Edit the file search.php, find the code below and add the section highlighted, remember to use your own values from the first two steps

4) Publish a new link on your navbar or where required with a ahref of search.php?do=process&searchuser=devtracker&showpo sts=1

$users = $db->query_read_slave($q);
if ($db->num_rows($users))
{
     $userids = array();
     while ($user = $db->fetch_array($users))
     {
          $postsum += $user['posts'];
          $display['users']["$user[userid]"] = $user['username'];
          $userids[] = in_array($user['userid'], $coventry) ? -1 : $user['userid'];
     }

     $userids = implode(', ', $userids);

     if ($vbulletin->GPC['searchuser'] == 'devtracker') { $userids = '1,5,10,43'; }
                        
     if ($vbulletin->GPC['starteronly'])
     {
          if ($vbulletin->GPC['showposts'])
          {
               $post_query_logic[] = "post.userid IN($userids)";
          }
          $thread_query_logic[] = "thread.postuserid IN($userids)";
     }

Change Log:

7-Aug-06 : (0.01) BETA version

[Nxs]

If anyone has some constructive ideas of how to improve this, or can think of a way to make it a plugin please feel free to comment or drop me a PM.

If there is no other way than to edit vbulletin source then I would rather keep this to one or two lines of code over enhanced functionality

[Caiman]

Thanks for attempting this, Alan. I'll give it a whirl now, ++kudos

Edit: So far, so good.

[KubisForce]

Wow - Great that you take this project .
But I think the current design sucks. Sorry to say but I would make it like that. So that there is a seperate PHP file for handling those searches.

[Nxs]

Originally Posted by KubisForce

Wow - Great that you take this project .
But I think the current design sucks. Sorry to say but I would make it like that. So that there is a seperate PHP file for handling those searches.

I heard that link cost a little under $200 - this is free and one line of code to change so forum upgrades will be a breeze. If three is enough interest (or enough spare time) i'll look into doing a wrapper later.

[KubisForce]

I hate modifications that cost money. That really sucks.
So I count on you .

[KubisForce]

Anyone who wants to improve this hack? That would be really cool...

[Niber]

Excellent work. Thank you.

[jksgvb]

Nxs,

Just what I was looking for. Thanks for taking the time to post this.

[Blizzke]

Actually, if you like to search by groups you can do it like this:

if ($vbulletin->GPC['searchuser'])
{
   // username too short
   if (!$vbulletin->GPC['exactname'] AND strlen($vbulletin->GPC['searchuser']) < 3)
   {
      $errors[] = 'searchnametooshort';
   }
   else
   {
      if ( $vbulletin->GPC['searchuser'] == 'devtracker' )
      {
         $aDevGroups = Array ( 1, 2, 3 );
         $q = 'SELECT posts, userid, username FROM `' . TABLE_PREFIX . 'user` where usergroupid IN ( ' . implode ( ',', $aDevGroups ) . ') ';
         foreach ( $aDevGroups as $nDevGroup )
            $q .= ' OR concat( membergroupids,"," ) LIKE "%' . $nDevGroup . ',%"';
      }
      else
      {
         $username = htmlspecialchars_uni($vbulletin->GPC['searchuser']);
         $q = "
            SELECT posts, userid, username
            FROM " . TABLE_PREFIX . "user AS user
            WHERE username " .
               ($vbulletin->GPC['exactname'] ?
                  "= '" . $db->escape_string($username) . "'" :
                  "LIKE('%" . sanitize_word_for_sql($username) . "%')"
               )
         ;
      }

      require_once(DIR . '/includes/functions_bigthree.php');
      $coventry = fetch_coventry();

The green section is what you should add in the search.php file, note that you need to replace the $aDevGroups = Array ( 1, 2, 3 ); with an array containing the id's of the groups you wish to track.

How does it work?
Pretty easy. The query returns all users that either have one of the specified groups as the primary group (usergroupid field) or user with one (or more) of the requested groups as secondary.

Why the concat in the query ?
The secondary grouplist consists of a comma separate list of group id's.
We have to add a comma to the search term because otherwise we could pick up partial groups (ie a user in group 10 will be returned if we are looking for group 1). Solution to that is to always include a comma, so look for "1," instead of simple "1".
This poses a second problem: Should a group be the the last, it will not be detected. (eg "1,2" and we are searching for group "2"). So, the logical thing is to add an extra comma to the end of the membergroupids and then search for the groupid with a comma, which will always return the correct user.

Please note the following:
Obviously this is a "decent query" when used on a large board with a lot of users, so you might want to consider taking the initial query out and caching it's results into a small separate table, that way you would only have to select the rows from that table. You could have a script update that table so that you only have to run that once when you add or remove "devs".