A good fix for Team BLOCK!

[Leinad4Mind]

I remember that version 1.0.3 worked in these Block. So I've recheck the SQL of 1.0.3 and put on 2.0.0b1.

I have changed:

Code: Select all

							u.user_id AS user_id, u.username AS username, u.user_colour AS user_colour, ug.group_id AS group_id
						FROM
							' . USERS_TABLE . ' AS u,
							' . USER_GROUP_TABLE . ' AS ug
						WHERE
							ug.user_id = u.user_id
							AND '. $db->sql_in_set('ug.group_id', $legends) . '
						ORDER BY u.username ASC';

To:

Code: Select all

							u.user_id, u.username, u.user_colour, u.group_id
						FROM
							' . USERS_TABLE . ' AS u
						WHERE
							'. $db->sql_in_set('u.group_id', $legends) . '
						ORDER BY u.username ASC';

And on both SQL versions we have a "Bug". The list doesn't appear with the correct order!

If I have: rui, miguel, Sara, Ana it appears:

Ana
Sara
miguel
rui

Culprit of lowercase and uppercase thing, so I've fix it:

Code: Select all

ORDER BY u.username ASC';

to

Code: Select all

ORDER BY UPPER(u.username) ASC';

(It transforms all to uppercase and then it order! And we don't need the "ASC" info, it's the default value...) the result is now correct:

Ana
miguel
rui
Sara

FINAL CODE:

Code: Select all

							u.user_id, u.username, u.user_colour, u.group_id
						FROM
							' . USERS_TABLE . ' AS u
						WHERE
							'. $db->sql_in_set('u.group_id', $legends) . '
						ORDER BY UPPER(u.username) ASC';

[Leinad4Mind]

My fix above is already OLD... so I've make a new one. ^^

I've installed the last version of Board3 Portal 2.0.0b1

And we still need to add this line on portal/modules/portal_leaders.php:

Code: Select all

							AND ug.group_id = u.group_id

Or else the same user would appear on other groups he belongs, and not only the default group selected on ACP.

The new code should look like this:

Code: Select all

				$sql = 'SELECT
							u.user_id AS user_id, u.username AS username, u.username_clean AS username_clean,
							u.user_colour AS user_colour, ug.group_id AS group_id
						FROM
							' . USERS_TABLE . ' AS u,
							' . USER_GROUP_TABLE . ' AS ug
						WHERE
							ug.user_id = u.user_id
							AND ug.group_id = u.group_id
							AND '. $db->sql_in_set('ug.group_id', $legends) . '
						ORDER BY u.username_clean ASC';
				$result = $db->sql_query($sql);

Cheers!