No more banned users Part 2
#1 28-11-2010 
So, banning people (or bots - which may or may not be people...) - its not fun and I don't like having a load of mess left behind.

I started off with the memberlist; clearing banned users - then I discovered that there are MORE traces of bannedness about.....

On the index page there is a nice little box that welcomes our newest members. Even if the newest member was subsequently banned before anyone else had time to join. Um yeah. Looked pretty daft.

Same on the board stats - welcome banned spammer!....Banned users show on the online list too, but thats a small issue and a tangled piece of code. So.

Priority one: Stop asking members to go and welcome a banned user.

I had two options here - just chop that code out - NO WELCOMES HERE - or - go find the code. Well, am I not the codefish? Indeed.

I had a good look around using this awesome resource (provided by and narrowed the areas I needed to change down to 2 files.

These were stats.php and the index.php. Now, I don't pretend to be a master coder by any means, and I am sure that there are better ways of doing this, but I can't work out how the plugin hooks its NOT a plugin, but a corefile edit. Thats a big minus, as it means that everytime I upgrade I have to keep those files safe. With plugins its a lot easier.

Anyhoo, to the code:

Stats php:

Step 1. Make a copy of your stats.php. Save it in a folder called originals
Step 2. Look at line 183
PHP Code:
$stats['newest_user'] = build_profile_link($stats['lastusername'], $stats['lastuid']); 

This line is the main query for the Board statistics; in case you are not sure which bit I mean, its this page.

The newest member needs to change to a user that is in your groups that you want to display. Following on from the awesome discovery of NOT IN and IN this code will be using this trick to select multiple groups.

It helps if we look to see what the code we are attempting to replace is actually doing. The query looks very small as there is already a call to the users table in the page, and lastusername and lastuid (last anything nearly) are pretty much standard code and run very fast. Thats the next point - I would be careful running this modified query on a big forum or a shared host.

Anyway - we are going to ADD a query of our own and do a tiny edit on an existing query, this will pull the data to the board stats.

Step 3: Go to line 144. Replace the db query with the below code:
PHP Code:
$query $db->query("
    SELECT u.uid, u.username, u.usergroup, COUNT(*) AS poststoday
    FROM "
.TABLE_PREFIX."posts p
.TABLE_PREFIX."users u ON (p.uid=u.uid)
.TABLE_PREFIX."userfields f ON (f.ufid=u.uid)
    WHERE u.usergroup NOT IN ('2','6') AND p.dateline > 
    GROUP BY p.uid ORDER BY poststoday DESC
    LIMIT 1

This adds the following: we now have user group included in our select and an exception for two groups. This means that a banned user cannot show as a top poster, and if you have any RSSbots about its best to put them in their own group and hide them away (IMO). This query will do that Smile

If you modify this query for your own use then you need to change the numbers in the brackets (2 and 6 in this example). If you want to add a group then just add a comma and a new number in ' quotes.

PHP Code:
WHERE u.usergroup NOT IN ('2','6','8','10') AND p.dateline $timesearch 

Step 4: Time to add a new query. Go to line 183

PHP Code:
// No banned users
$bfxquery $db->query("
    SELECT u.*, f.*, u.usergroup
    FROM "
.TABLE_PREFIX."users u
.TABLE_PREFIX."userfields f ON (f.ufid=u.uid)
    WHERE u.usergroup NOT IN ('2','6')
    ORDER BY u.uid DESC
    LIMIT 1
$bfx $db->fetch_array($bfxquery))
$stats['newest_user'] = build_profile_link($bfx['username'], $bfx['uid']);
$bfx $lang->nobody;

Step 5: Adjust usergroup numbers to suit, and don't forget to change this line:

PHP Code:

The value should be one of your excluded groups - its a stupid trick to make sure the result of the query can never be false. I tried taking it out and the edit stopped working. Its not doing any harm AFAIK.

Step 6: Upload to your forum root, overwriting the stats.php.

Check it out on your forum Smile

The site don't jive? PRESS F5 Flower


Sorry, that is a members only option