https://www.icyphoenix.com/viewtopic.php?f=26&t=8358
-----------------------------------
Joshua203
Thu 02 Feb, 2012 02:42

Exclude banned Calendar Bdays?
-----------------------------------
Hey all,

I picked up an old project again and I don't really see how I can exclude birthdays of banned users from showing up.

The function file content I'm looking at is this part:
[code linenumbers=false]/* Generates the list of birthdays for the given date
*/
function generate_birthday_list( $day, $month, $year )
{
	global $db, $user, $config;

	$birthday_list = "";
	if ($config['load_birthdays'] && $config['allow_birthdays'])
	{
		$sql = 'SELECT user_id, username, user_colour, user_birthday
				FROM ' . USERS_TABLE . "
				WHERE user_birthday LIKE '" . $db->sql_escape(sprintf('%2d-%2d-', $day, $month)) . "%'
				AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ')';
		$result = $db->sql_query($sql);
		while ($row = $db->sql_fetchrow($result))
		{
			// TBD TRANSLATION ISSUE HERE!!!
			$birthday_list .= (($birthday_list != '') ? ', ' : '') . get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']);
			if ($age = (int) substr($row['user_birthday'], -4))
			{
				// TBD TRANSLATION ISSUE HERE!!!
				$birthday_list .= ' (' . ($year - $age) . ')';
			}
		}
		if( $birthday_list != "" )
		{
			// TBD TRANSLATION ISSUE HERE!!!
			$birthday_list = $user->lang['BIRTHDAYS'].": ". $birthday_list;
		}
		$db->sql_freeresult($result);
	}

	return $birthday_list;
}
[/code]

I did manage to exclude them from showing in my portals but the code used there is quite different :(

If anyone has any idea it would make me very happy because this calendar mod has been abandoned a long time ago.


-----------------------------------
TheSteffen
Thu 02 Feb, 2012 20:22

Re: Exclude banned Calendar Bdays?
-----------------------------------
Well, I am not the best with php but can you play with this

[code linenumbers=false]AND user_active = 1[/code]

Because banned users should have 0 , I guess


-----------------------------------
Joshua203
Fri 03 Feb, 2012 08:28

Re: Exclude banned Calendar Bdays?
-----------------------------------
Thanks for the suggestion Steffen but this won't work on phpbb .. as far as I know this does not exist in my DB


-----------------------------------
TheSteffen
Fri 03 Feb, 2012 09:09

Re: Exclude banned Calendar Bdays?
-----------------------------------
Hehehe, on phpBB3 you should have a look into phpbb_banlist => ban_userid

But I dont know how to change your code  :(

EDIT: Maybe you can combine with this mod

[b]Hide Banned Members From Memberlist on phpBB[/b]

OPEN [b]memberlist.php[/b]
FIND
[codeblock]// Get us some users :D[/codeblock]
Add before
[codeblock]//Begin: banned users hidden in memberlist_body
$sql = 'SELECT ban_userid
        FROM ' . BANLIST_TABLE . '
        WHERE ban_userid <> 0
        AND (ban_end >= ' . time() . ' OR ban_end = 0)';
$result = $db->sql_query($sql);      

$ban_list[] = array(0);    
while ($row = $db->sql_fetchrow($result))
{
    $ban_list[] = $row['ban_userid'];
}   
$db->sql_freeresult($result);
//End: banned users hidden in memberlist_body [/codeblock]

FIND
[codeblock]                    $user_list[] = (int) $row['user_id'];[/codeblock]
REPLACE WITH
[codeblock]                if (!in_array((int) $row['user_id'], $ban_list))//ban_list test line
                {
                    $user_list[] = (int) $row['user_id'];
                }  [/codeblock]


-----------------------------------
Joshua203
Fri 03 Feb, 2012 18:40

Re: Exclude banned Calendar Bdays?
-----------------------------------
Thanks for the suggestion Steffen, however after looking at your suggestion I took another look at what I did to the portal in the past and maybe I got it fixed now ... the calendar looks a lot cleaner now but not completely missing birthdays.

I still have to test and investigate but I was so excited that I wanted to reply  :lol: 

It would be great if anyone could tell me "yeah correct" or "no stupid!" so below is what I the changed code....

[code linenumbers=true]/* Generates the list of birthdays for the given date
*/
function generate_birthday_list( $day, $month, $year )
{
	global $db, $user, $config;

	$birthday_list = "";
	if ($config['load_birthdays'] && $config['allow_birthdays'])
	{
		$sql = 'SELECT user_id, username, user_colour, user_birthday
				FROM ' . USERS_TABLE . '
				LEFT JOIN ' . BANLIST_TABLE . " b ON (user_id = b.ban_userid)
				WHERE (b.ban_id IS NULL
				OR b.ban_exclude = 1)
				AND user_birthday LIKE '" . $db->sql_escape(sprintf('%2d-%2d-', $day, $month)) . "%'
				AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ')';
		$result = $db->sql_query($sql);
		while ($row = $db->sql_fetchrow($result))
		{
			// TBD TRANSLATION ISSUE HERE!!!
			$birthday_list .= (($birthday_list != '') ? ', ' : '') . get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']);
			if ($age = (int) substr($row['user_birthday'], -4))
			{
				// TBD TRANSLATION ISSUE HERE!!!
				$birthday_list .= ' (' . ($year - $age) . ')';
			}
		}
		if( $birthday_list != "" )
		{
			// TBD TRANSLATION ISSUE HERE!!!
			$birthday_list = $user->lang['BIRTHDAYS'].": ". $birthday_list;
		}
		$db->sql_freeresult($result);
	}

	return $birthday_list;
}[/code]

changes:
- in line [color=#00ff00]5[/color] I replaced the [color=red]"[/color] by a [color=red]'[/color]
- line [color=#00ff00]6, 7 and 8[/color] are newly added
- in line [color=#00ff00]9[/color] I replaced the [color=red]WHERE[/color] into an [color=red]AND[/color]

I truly can't believe I didn't goof anything up so please anyone judge it  :lol:


-----------------------------------
Informpro
Sat 04 Feb, 2012 00:12

Re: Exclude Banned Calendar Bdays?
-----------------------------------
$sql = 'SELECT user_id, username, user_colour, user_birthday 
                FROM ' . USERS_TABLE . ' 
                LEFT JOIN ' . BANLIST_TABLE . " b ON (user_id = b.ban_userid) 
                WHERE (b.ban_id IS NULL 
                OR b.ban_exclude = 1) 
                AND user_birthday LIKE '" . $db->sql_escape(sprintf('%2d-%2d-', $day, $month)) . "%' 
                AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ')';

maybe just
AND user_id NOT in (SELECT ban_userid FROM " . BANLIST_TABLE . ")
I don't have mysql here, so I can't "explain" queries but I think your one is better.


-----------------------------------
Joshua203
Sat 04 Feb, 2012 17:28

Re: Exclude banned Calendar Bdays?
-----------------------------------
Thanks for another suggestion Informpro, yours does look alittle more "straight to the point" 

If my own try starts showing flaws I will certainly try your idea!

Stuff I mostly doubt are in the use of the quotes and double quotes, I know almost nothing about all this stuff .. it's all just copy, paste, try to adjust and wait for errors to pop up for me TBH  :wink:


-----------------------------------
Informpro
Sun 05 Feb, 2012 21:28

Re: Exclude Banned Calendar Bdays?
-----------------------------------
There's actually too many way to achieve that. If you can run EXPLAIN on these queries, you'll have a better idea of the performance impact. You could also select in before (=> (SELECT bla FROM ...) as B), etc, etc.


-----------------------------------
Joshua203
Wed 08 Feb, 2012 04:20

Re: Exclude banned Calendar Bdays?
-----------------------------------
I'll mark this solved before I get even more confused  :lol: 

Thanks for all your replies guys :wink:


