Hide banned accounts from Highscores

[pink_panther]

I want to exclude anyone with an active BAN. Needs to exclude any player who’s account ID exists in account_bans and the current time (epoch) is < account_bans.expires_at time

Heres the currenct query.

SELECT p.name, p.level, p.vocation, za.flag, ps.value AS value FROM players AS p INNER JOIN znote_accounts AS za ON p.account_id=za.account_id INNER JOIN player_storage AS ps ON (p.id=ps.player_id AND ps.key = 15999) WHERE p.group_id < 4 ORDER BY ps.value DESC LIMIT 0, 300;

 

[Peonso]

Replace b.account_id depending on your ban table. I might have fucked up join syntax, but should be something like that.

$timeNow = time();
SELECT p.name, p.level, p.vocation, za.flag, ps.value AS value FROM players AS p INNER JOIN znote_accounts AS za ON p.account_id=za.account_id INNER JOIN player_storage AS ps ON (p.id=ps.player_id AND ps.key = 15999) LEFT OUTER JOIN acount_bans AS b ON p.account_id=b.account_id WHERE p.group_id < 4 AND WHERE b.expires_at < $timeNow OR b.expires_at=NULL ORDER BY ps.value DESC LIMIT 0, 300;

 

[pink_panther]

I altered slightly.

This appears to be working as required

SELECT p.name, p.level, p.vocation, za.flag, ps.value AS value FROM players AS p
    INNER JOIN znote_accounts AS za ON p.account_id=za.account_id
    INNER JOIN player_storage AS ps ON (p.id=ps.player_id AND ps.key = 15999)
    WHERE p.group_id < 4
    AND (p.account_id NOT IN(SELECT account_id FROM account_bans WHERE account_bans.expires_at > UNIX_TIMESTAMP()))
    ORDER BY ps.value
    DESC LIMIT 0, 300;

Basically I just wanna add this to my SELECTs for highscores

    AND (p.account_id NOT IN(SELECT account_id FROM account_bans WHERE account_bans.expires_at > UNIX_TIMESTAMP() or account_bans.expires_at = 0))