No more Banned users
#1 28-11-2010 
Yay! I worked it out (am superpleased).... worked what out you ask?

Well, we get some spambots around the place (all sites do - most irritating) and of course I ban them pretty promptly, but I hated the way they showed on my memberlist, lots of banned users with stupid names. Of course, I *could* just delete them, but then the no banned users re-registering software won't kick in - which means they keep returning.

So I thought hrmmm, there MUST be a way to get rid of those banned types from the memberlist. Its silly to not use a neat bit of anti-spam software because you want a pretty list.

So I made this query. Go to line 218 of your memberlist.php and edit to match Smile

PHP Code:
$query $db->query("
    SELECT u.*, f.*, u.usergroup
    FROM "
.TABLE_PREFIX."users u
    LEFT JOIN "
.TABLE_PREFIX."userfields f ON (f.ufid=u.uid)
    WHERE 
{$search_query} AND u.usergroup !='2'
    ORDER BY 
{$sort_field} {$sort_order}
    LIMIT 
{$start}{$per_page}
    "
); 

The query says
Quote:"select the user and the userfields and the usergroup from the specified tables and, based on the search query, get the users except the ones in group 2, and output the data"

It worked! yay, no more banned users!

Then I saw wikibot on there -and I did not really want to have that bot showing on the memberlist (its my personal robot slave, it wanders the web grabbing info for me and posting stuff to my private forum). Thats where things got interesting - what was the correct syntax to show all members EXCEPT the wikibot and the spammers? You would have thought (if you are a noob like me) that this query would do it:

PHP Code:
$query $db->query("
    SELECT u.*, f.*, u.usergroup
    FROM "
.TABLE_PREFIX."users u
    LEFT JOIN "
.TABLE_PREFIX."userfields f ON (f.ufid=u.uid)
    WHERE 
{$search_query} AND u.usergroup !='2' OR !='9'
    ORDER BY 
{$sort_field} {$sort_order}
    LIMIT 
{$start}{$per_page}
    "
); 

Well it doesn't. What it is asking is

Quote: "select the user and the userfields and the usergroup from the specified tables and, based on the searchquery, get the users except the ones where their group is sometimes 2 or sometimes 9, and output the data"

This is effectively NO users, so the spammers are back on the list. And the bot. GRRRRR.....ponder.....SQL Manual.....(flip....flip....) AHA.

For this kind of query we need an array, which is SQL speak for some brackets round a CSV list, and a command about the array. There are two ways of phrasing this query - get all of those who ARE in usergroups 2 and 9, or get all of those who AREN'T in usergroups 2 and 9.

Well, assuming that we have more usergroups that we DO want to include, then its shorter and neater to EXCLUDE the groups. Enter the SQL command IN. Or, in our case, NOT IN.

PHP Code:
$query $db->query("
    SELECT u.*, f.*, u.usergroup
    FROM "
.TABLE_PREFIX."users u
    LEFT JOIN "
.TABLE_PREFIX."userfields f ON (f.ufid=u.uid)
    WHERE 
{$search_query} AND u.usergroup NOT IN ('2','9')
    ORDER BY 
{$sort_field} {$sort_order}
    LIMIT 
{$start}{$per_page}
"
); 

Ta-dah! No banned users on the memberlist (take a look)


PART TWO: http://www.leefish.nl/mybb/showthread.ph...1&pid=1937
The site don't jive? PRESS F5 Flower

0
#2 28-11-2010 
Gah - more of them - I have found them on the stats page and the index page....grrr
The site don't jive? PRESS F5 Flower

0
#3 19-10-2012 
  • Registered
  • 0
  • 1
Just wanted to say thanks, this proved very useful to me, been struggling for a long time to remove a couple user groups from the memberlist


sorry for necro'ing this old thread.

0
#4 19-10-2012 
Not a problem - I keep these posts for my benefit too - its sort of like a code repo for me Big Grin
The site don't jive? PRESS F5 Flower

0


Sorry, that is a members only option