Help with forums sharing same database.

Discussion forum for MOD Writers regarding MOD Development.
Locked
Gabe Logan
Registered User
Posts: 17
Joined: Sat Jan 07, 2006 7:36 pm

Re: Help with forums sharing same database.

Post by Gabe Logan » Mon Aug 04, 2008 5:53 pm

Any possibility that we find a way so that message count would consider posts from all forums ? It seems weird to me that the same user would have 2 or more ranks, depending on the forum.

Or, if messages are counted seperately on each forum, would it be possible to make the ranks take into account all messages?

I guess it implies a lot more modding/editing than just setting up new fields in the user table, but the results would obviously be more satisfying.

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Tue Aug 05, 2008 7:24 am

you're right.

I'm currently working on sharing user among boards for phpBB SEO, and I was about to look toward this direction. I'll keep you posted.

A new trick that could help some using a dedicated server (or a shared one with multi db settings) to make forums even easier to maintain is to use one db per forum (must be on the same db server, only tested on mysql), with a single db account or, with db accounts having enough priviledges on all dbs, and then to just add the table names in front of the table prefixes :

Code: Select all

$table_prefix = 'firstboard.phpbb_';
$table_shared = 'secondboard.phpbb_';
In this example, "firstboard" and "secondboard" are our two dbs, we do not even need to use different table prefix for each forums this way. And all dbs are accessed within a single connexion :D

phpBB will work exactly the same, and the nice thing is that we can easily re-separate forums when we want, just a matter of importing the shared table again in all dbs and to go back to table_prefix = table_shared.

Can really be handy to share user among more than two forums, because it can be a real mess to run a single db with 4 occurrences of each table with different prefixes.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Tue Aug 05, 2008 8:35 am

Seemed easier than I though, in includes/acp/acp_main.php, you can replace :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
with :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);
                        $user_data = array();
                        while ($row = $db->sql_fetchrow($result))
                        {
                            $user_data[$row['user_id']] = (int) $row['num_posts'];
                        }
                        $db->sql_freeresult($result);
                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  properprefix_posts p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            if (!empty($user_data[$row['user_id']]))
                            {
                                $row['num_posts'] += $user_data[$row['user_id']];
                            }
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
In this you just have to set the properprefix_posts to the proper value for the second board.
And so on for all others.
Note that the original code is a bit heavy for very large board, it's performing a query per user so ...
Adding the extra counting does not change much though, I tested this with over 5000 users without issues.

And the good thing is that the user post pages will only consider the local amount, so results will stay consistent, while the total amount will be kept in user profiles ;)

Enjoy :D

++
Last edited by dcz on Tue Aug 05, 2008 12:36 pm, edited 1 time in total.

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

mcvetyty
Registered User
Posts: 34
Joined: Sat Feb 16, 2008 10:06 pm

Re: Help with forums sharing same database.

Post by mcvetyty » Tue Aug 05, 2008 12:23 pm

dcz wrote:Seemed easier than I though, in includes/acp/acp_main.php, you can replace :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
with :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);
                        $user_data = array();
                        while ($row = $db->sql_fetchrow($result))
                        {
                            $user_data[$row['user_id']] = $row['num_posts'];
                        }
                        $db->sql_freeresult($result);
                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  properprefix_posts p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            $row['num_posts'] += $user_data[$row['user_id']];
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
In this you just have to set the properprefix_posts to the proper value for the second board.
And so on for all others.
Note that the original code is a bit heavy for very large board, it's performing a query per user so ...
Adding the extra counting does not change much though, I tested this with over 5000 users without issues.

And the good thing is that the user post pages will only consider the local amount, so results will stay consistent, while the total amount will be kept in user profiles ;)

Enjoy :D

++

Hi dcz,

This makes it so that the post counts are the same per user across multiple boards, is that correct?

Do you have any insight on how to make the "Total Posts","Total Members", and "Newest Member" work accurately that is located at the bottom of each board without having to constantly clear cache?

Thanks in advance!

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Tue Aug 05, 2008 12:48 pm

This makes it so that the post counts are the same per user across multiple boards, is that correct?
Yes.

By the way, I edited the post above to add some sanity checks in the code, not that it could cause trouble but could through some notice about the user_data count to be not set in some of the shared forums.

For the cache thing, you could clear every boards's cache at once, adding something like :

Code: Select all

        @unlink('path_to_the_slave_forum/cache/' . basename($filename)); 
after :

Code: Select all

    /**
    * Removes/unlinks file
    */
    function remove_file($filename, $check = false)
    { 
in :

Code: Select all

includes/acm/acm_file.php
Should work to clear the same file name's cache file currently cleared in the other forum cache dir, if the server's set up allows you to. To make it short, it will in most cases imply that all forums are installed within the same ftp account to work.

Will of course cause unlink to unlink nothing in some cases, but I'm pretty sure it's faster to directly unlink than to first check if the target file exists so.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

mcvetyty
Registered User
Posts: 34
Joined: Sat Feb 16, 2008 10:06 pm

Re: Help with forums sharing same database.

Post by mcvetyty » Tue Aug 05, 2008 10:54 pm

dcz wrote:
This makes it so that the post counts are the same per user across multiple boards, is that correct?
Yes.

By the way, I edited the post above to add some sanity checks in the code, not that it could cause trouble but could through some notice about the user_data count to be not set in some of the shared forums.

For the cache thing, you could clear every boards's cache at once, adding something like :

Code: Select all

        @unlink('path_to_the_slave_forum/cache/' . basename($filename)); 
after :

Code: Select all

    /**
    * Removes/unlinks file
    */
    function remove_file($filename, $check = false)
    { 
in :

Code: Select all

includes/acm/acm_file.php
Should work to clear the same file name's cache file currently cleared in the other forum cache dir, if the server's set up allows you to. To make it short, it will in most cases imply that all forums are installed within the same ftp account to work.

Will of course cause unlink to unlink nothing in some cases, but I'm pretty sure it's faster to directly unlink than to first check if the target file exists so.

++
Hey there dcz,

This is good stuff! That at least makes it easier to clear the cache then going from board to board..

Is there no way, however, for you to think of having this done automatically in the sense where if one board gets a registration it updates all of them at the same time?

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Tue Aug 05, 2008 11:19 pm

The trick I mentioned should do, when board A tries to trash a cache file, the code will attempt to trash the same cache file name in board B's cache dir, it will at least work for the common caching things such as phpbb config (data_global and basically all the data_* files), the script could filter template's cache, but it's not big deal if you deleted a bit more than you want here, only the sql_ cache files could be missed, but they do not content the stats data, so it's no big deal.

Being able to use this is only a matter of setting the proper path to board B (could require an absolute path to make it simple) and to have the proper server settings (eg same ftp account for most).

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

Gabe Logan
Registered User
Posts: 17
Joined: Sat Jan 07, 2006 7:36 pm

Re: Help with forums sharing same database.

Post by Gabe Logan » Sat Aug 09, 2008 9:08 pm

dcz wrote:Seemed easier than I though, in includes/acp/acp_main.php, you can replace :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
with :

Code: Select all

                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);
                        $user_data = array();
                        while ($row = $db->sql_fetchrow($result))
                        {
                            $user_data[$row['user_id']] = (int) $row['num_posts'];
                        }
                        $db->sql_freeresult($result);
                        $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id
                            FROM ' . USERS_TABLE . ' u
                            LEFT JOIN  properprefix_posts p ON (u.user_id = p.poster_id AND p.post_postcount = 1)
                            GROUP BY u.user_id';

                        $result = $db->sql_query($sql);

                        while ($row = $db->sql_fetchrow($result))
                        {
                            if (!empty($user_data[$row['user_id']]))
                            {
                                $row['num_posts'] += $user_data[$row['user_id']];
                            }
                            $db->sql_query('UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}");
                        }
                        $db->sql_freeresult($result); 
In this you just have to set the properprefix_posts to the proper value for the second board.
And so on for all others.
Note that the original code is a bit heavy for very large board, it's performing a query per user so ...
Adding the extra counting does not change much though, I tested this with over 5000 users without issues.

And the good thing is that the user post pages will only consider the local amount, so results will stay consistent, while the total amount will be kept in user profiles ;)

Enjoy :D

++
This is great! Seems to work perfectly for me too! The only thing I have tested yet is if I try to reset user message count from the ACP, will it set the message count of users who haven't posted on the forum which I'm working from to 0? Or does your Mod also solve that?

Now the only issue that still bothers me is that when I log in one forum, I'm am not logged onto the other one. Why aren't sessions shared from one board to the other?

Edit: Just noticed a strange detail concerning the total post count. In the user's profile page, the % of all posts does take into account the number of messages posted but the user, but divides it on the total amount of posts of the board, whether or not that user has posted on it. Let's say that Mister X posted 3 message on one board. If I then look at his profile from the other board (let say it has a total of 10 messages), it will still say that Mister X has contributed with 3.33% of the all posts. It would be better if that % could be calculated over the total of all boards combined.

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Wed Aug 13, 2008 12:31 pm

If sessions are not shared directly, it can come from the cookie and domain settings.

So, if all boards are using the same domain or, sub domains from the same domain, sessions should be shared directly as long as the cookie settings are correct eg :

Code: Select all

cookie domain : .example.com (with the dot ".")
cookie path : /
for all boards.

And the cookie name should as well be the same for all boards.

Now if you have different domains used by the boards, it won't be possible to share the session automatically, user will have to re login within each boards because the cookie cannot be shared among domains (only sub domains).

About the percent of total post, I guess that this one will be hard to deal with, that's the problem with synchronizing post count like we just did.
You could add on extra local post count field in the user table per forum to use it for the local stats, but this would imply quite some code change.

Another way to deal with this would be to do it dynamically, with some extra code to count the local post count on search user pages and profiles, and eventually to display links to the other's boards search page and profile.

Not really a big issue, but I may provide with some code to do it if I end up implementing it on phpBB SEO.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

Gabe Logan
Registered User
Posts: 17
Joined: Sat Jan 07, 2006 7:36 pm

Re: Help with forums sharing same database.

Post by Gabe Logan » Wed Aug 13, 2008 4:00 pm

Indeed, it might not be worth it if it implies that much coding. It's not something many users will notice. It's already nice the way it is.

And my sharing-session problem is solved, thanks to you! I had different cookie names, which explained everything.

User avatar
Anubis_The_Jackal
Registered User
Posts: 127
Joined: Sun Mar 30, 2003 9:59 am

Re: Help with forums sharing same database.

Post by Anubis_The_Jackal » Thu Sep 18, 2008 4:30 am

Here we go...


I have been trying this, and met with much success. I am gradually separating the users apart right now, with each site recording separated post counts, stats, and everything else. However I have run into a massive problem. I went about my usual method (replacing user_ with alpha_ and beta_ and doing that in all files affected) however this one has become a nightmare.

user_colour. It's the Darth Vader of separation. I have replaced all references to it, and deleted the value replacing it with alpha_colour. Well, lo and behold its still looking for user_colour.

I think I have tracked down the culprit, but am now at a major impasse.

$group_attributes

That little you-know-what seems to be the reason why. I have replaced every instance of the utterance of user_colour - but that thing still demands blood! I need to find where I can change this, modify it, or just blast it.

Here is where I found it being a d-bag

Code: Select all

function group_user_attributes($action, $group_id, $user_id_ary = false, $username_ary = false, $group_name = false, $group_attributes = false)
{
	global $db, $auth, $phpbb_root_path, $phpEx, $config;

	// We need both username and user_id info
	$result = user_get_id_name($user_id_ary, $username_ary);

	if (!sizeof($user_id_ary) || $result !== false)
	{
		return 'NO_USERS';
	}

	if (!$group_name)
	{
		$group_name = get_group_name($group_id);
	}

	switch ($action)
	{
		case 'demote':
		case 'promote':

			$sql = 'SELECT user_id FROM ' . USER_GROUP_TABLE . "
				WHERE group_id = $group_id
					AND user_pending = 1
					AND " . $db->sql_in_set('user_id', $user_id_ary);
			$result = $db->sql_query_limit($sql, 1);
			$not_empty = ($db->sql_fetchrow($result));
			$db->sql_freeresult($result);
			if ($not_empty)
			{
				return 'NO_VALID_USERS';
			}

			$sql = 'UPDATE ' . USER_GROUP_TABLE . '
				SET group_leader = ' . (($action == 'promote') ? 1 : 0) . "
				WHERE group_id = $group_id
					AND user_pending = 0
					AND " . $db->sql_in_set('user_id', $user_id_ary);
			$db->sql_query($sql);

			$log = ($action == 'promote') ? 'LOG_GROUP_PROMOTED' : 'LOG_GROUP_DEMOTED';
		break;

		case 'approve':
			// Make sure we only approve those which are pending ;)
			$sql = 'SELECT u.user_id, u.user_email, u.username, u.username_clean, u.user_notify_type, u.user_jabber, u.user_lang
				FROM ' . USERS_TABLE . ' u, ' . USER_GROUP_TABLE . ' ug
				WHERE ug.group_id = ' . $group_id . '
					AND ug.user_pending = 1
					AND ug.user_id = u.user_id
					AND ' . $db->sql_in_set('ug.user_id', $user_id_ary);
			$result = $db->sql_query($sql);

			$user_id_ary = $email_users = array();
			while ($row = $db->sql_fetchrow($result))
			{
				$user_id_ary[] = $row['user_id'];
				$email_users[] = $row;
			}
			$db->sql_freeresult($result);

			if (!sizeof($user_id_ary))
			{
				return false;
			}

			$sql = 'UPDATE ' . USER_GROUP_TABLE . "
				SET user_pending = 0
				WHERE group_id = $group_id
					AND " . $db->sql_in_set('user_id', $user_id_ary);
			$db->sql_query($sql);

			// Send approved email to users...
			include_once($phpbb_root_path . 'includes/functions_messenger.' . $phpEx);
			$messenger = new messenger();

			foreach ($email_users as $row)
			{
				$messenger->template('group_approved', $row['user_lang']);

				$messenger->to($row['user_email'], $row['username']);
				$messenger->im($row['user_jabber'], $row['username']);

				$messenger->assign_vars(array(
					'USERNAME'		=> htmlspecialchars_decode($row['username']),
					'GROUP_NAME'	=> htmlspecialchars_decode($group_name),
					'U_GROUP'		=> generate_board_url() . "/ucp.$phpEx?i=groups&mode=membership")
				);

				$messenger->send($row['user_notify_type']);
			}

			$messenger->save_queue();

			$log = 'LOG_USERS_APPROVED';
		break;

		case 'default':
			$sql = 'SELECT user_id, group_id FROM ' . USERS_TABLE . '
				WHERE ' . $db->sql_in_set('user_id', $user_id_ary, false, true);
			$result = $db->sql_query($sql);

			$groups = array();
			while ($row = $db->sql_fetchrow($result))
			{
				if (!isset($groups[$row['group_id']]))
				{
					$groups[$row['group_id']] = array();
				}
				$groups[$row['group_id']][] = $row['user_id'];
			}
			$db->sql_freeresult($result);

			foreach ($groups as $gid => $uids)
			{
				remove_default_rank($gid, $uids);
				remove_default_avatar($gid, $uids);
			}
			group_set_user_default($group_id, $user_id_ary, $group_attributes);
			$log = 'LOG_GROUP_DEFAULTS';
		break;
	}

	// Clear permissions cache of relevant users
	$auth->acl_clear_prefetch($user_id_ary);

	add_log('admin', $log, $group_name, implode(', ', $username_ary));

	group_update_listings($group_id);

	return false;
}
in fact right here

Code: Select all

group_set_user_default($group_id, $user_id_ary, $group_attributes);
if I change the variable $group_attributes it lets me set default groups again, but doesn't do a darn thing to the color. It is ruining the work I put into this, so if someone here who is more enlightened could shed a beam for me, I would be much appreciated.

Eventually I do plan on having more than four forums set up (alpha, beta, gamma, delta) which are on different domains (I already have one working since it is on the same server) creating a nice little multi-site multi-access community.

However, I need colors fixed. Badly. It belches out errors when I put people in groups and likes to scare them. It must die.

Edit: Forgot to mention I am also using separate databases for everything I am running, however they are all connected to a main database with the user table.
PHPBB since 03
Jackuul Empire-MSRP V

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: Help with forums sharing same database.

Post by dcz » Thu Sep 18, 2008 9:31 am

You mean that you want to allow your user to have different colors on all forums, for example because of the local group to be different and have different colors ?

So you are adding one extra color row for each user, eg user_colour_alpha etc ?

Have you mass searched for the string :

Code: Select all

'user_colour'
in all the php file, with for example search & replace ?

I guess you'd have to make sure the proper key is used every time the user_color is needed.

As well, if you did not do it already, you could define a constant to hold the local key name :

Code: Select all

define('LOCAL_KEY', '_alpha');
in config.php.

This way you could use it in the php file to attach the extra key in all altered fields names, and only mod your files once, you'd only have to change it's value in config.php to set up each forum properly.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
Anubis_The_Jackal
Registered User
Posts: 127
Joined: Sun Mar 30, 2003 9:59 am

Re: Help with forums sharing same database.

Post by Anubis_The_Jackal » Thu Sep 18, 2008 2:59 pm

I am mass using it as I explained, however I renamed them alpha_color, and beta_color. I have mass edited, trust me. But it still wishes to use user_color. As stated, the problem occurs with $group_attributes in functions_user.php in the line I quoted. I need to change this to work, however I do not know how or where to change $group_attributes as I have searched for everything related to it in the files and come up empty handed.
PHPBB since 03
Jackuul Empire-MSRP V

User avatar
Anubis_The_Jackal
Registered User
Posts: 127
Joined: Sun Mar 30, 2003 9:59 am

Re: Help with forums sharing same database.

Post by Anubis_The_Jackal » Fri Sep 19, 2008 2:20 am

I think I may or may not have found it...

Code: Select all

settype($group_attributes[$attribute], $type);
			$sql_ary[str_replace('group_', 'user_', $attribute)] = $group_attributes[$attribute];
Not exactly sure if this is right as it was in the much lower lines of my functions_user down toward the 3100s, but if I am not mistaken it is telling it to replace anything_ with either group_ or user_

However I am unsure if I am correct in this assumption.
PHPBB since 03
Jackuul Empire-MSRP V

User avatar
Anubis_The_Jackal
Registered User
Posts: 127
Joined: Sun Mar 30, 2003 9:59 am

Re: Help with forums sharing same database.

Post by Anubis_The_Jackal » Fri Sep 19, 2008 2:22 am

For this document (and the forum to make sure I did replace everything) I changed the user_colour and alpha_colour to msrp_colour so I could better keep track of which was for which.

Oh, and here is the full line mine came from

Code: Select all

function group_set_user_default($group_id, $user_id_ary, $group_attributes = false, $update_listing = false)
{
	global $db;

	if (empty($user_id_ary))
	{
		return;
	}

	$attribute_ary = array(
		'group_colour'			=> 'string',
		'group_rank'			=> 'int',
		'group_avatar'			=> 'string',
		'group_avatar_type'		=> 'int',
		'group_avatar_width'	=> 'int',
		'group_avatar_height'	=> 'int',
	);

	$sql_ary = array(
		'group_id'		=> $group_id
	);

	// Were group attributes passed to the function? If not we need to obtain them
	if ($group_attributes === false)
	{
		$sql = 'SELECT ' . implode(', ', array_keys($attribute_ary)) . '
			FROM ' . GROUPS_TABLE . "
			WHERE group_id = $group_id";
		$result = $db->sql_query($sql);
		$group_attributes = $db->sql_fetchrow($result);
		$db->sql_freeresult($result);
	}

	foreach ($attribute_ary as $attribute => $type)
	{
		if (isset($group_attributes[$attribute]))
		{
			// If we are about to set an avatar or rank, we will not overwrite with empty, unless we are not actually changing the default group
			if ((strpos($attribute, 'group_avatar') === 0 || strpos($attribute, 'group_rank') === 0) && !$group_attributes[$attribute])
			{
				continue;
			}

			settype($group_attributes[$attribute], $type);
			$sql_ary[str_replace('group_', 'user_', $attribute)] = $group_attributes[$attribute];
		}
	}

	// Before we update the user attributes, we will make a list of those having now the group avatar assigned
	if (isset($sql_ary['user_avatar']))
	{
		// Ok, get the original avatar data from users having an uploaded one (we need to remove these from the filesystem)
		$sql = 'SELECT user_id, group_id, user_avatar
			FROM ' . USERS_TABLE . '
			WHERE ' . $db->sql_in_set('user_id', $user_id_ary) . '
				AND user_avatar_type = ' . AVATAR_UPLOAD;
		$result = $db->sql_query($sql);

		while ($row = $db->sql_fetchrow($result))
		{
			avatar_delete('user', $row);
		}
		$db->sql_freeresult($result);
	}
	else
	{
		unset($sql_ary['user_avatar_type']);
		unset($sql_ary['user_avatar_height']);
		unset($sql_ary['user_avatar_width']);
	}

	$sql = 'UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
		WHERE ' . $db->sql_in_set('user_id', $user_id_ary);
	$db->sql_query($sql);

	if (isset($sql_ary['msrp_colour']))
	{
		// Update any cached colour information for these users
		$sql = 'UPDATE ' . FORUMS_TABLE . " SET forum_last_poster_colour = '" . $db->sql_escape($sql_ary['msrp_colour']) . "'
			WHERE " . $db->sql_in_set('forum_last_poster_id', $user_id_ary);
		$db->sql_query($sql);

		$sql = 'UPDATE ' . TOPICS_TABLE . " SET topic_first_poster_colour = '" . $db->sql_escape($sql_ary['msrp_colour']) . "'
			WHERE " . $db->sql_in_set('topic_poster', $user_id_ary);
		$db->sql_query($sql);

		$sql = 'UPDATE ' . TOPICS_TABLE . " SET topic_last_poster_colour = '" . $db->sql_escape($sql_ary['msrp_colour']) . "'
			WHERE " . $db->sql_in_set('topic_last_poster_id', $user_id_ary);
		$db->sql_query($sql);
		global $cache, $phpbb_root_path, $phpEx, $table_prefix;
		include($phpbb_root_path . 'includes/arcade/arcade_constants.' . $phpEx);
		$sql = 'UPDATE ' . ARCADE_CATS_TABLE . " SET cat_last_play_user_colour = '" . $db->sql_escape($sql_ary['msrp_colour']) . "'
			WHERE " . $db->sql_in_set('cat_last_play_user_id', $user_id_ary);
		$db->sql_query($sql);

		$cache->destroy('sql', ARCADE_CATS_TABLE);
		$cache->destroy('sql', ARCADE_GAMES_TABLE);
		$cache->destroy('sql', ARCADE_SCORES_TABLE);
		$cache->destroy('_arcade_leaders');
		global $config;

		if (in_array($config['newest_user_id'], $user_id_ary))
		{
			set_config('newest_msrp_colour', $sql_ary['msrp_colour'], true);
		}
	}

	if ($update_listing)
	{
		group_update_listings($group_id);
	}
}

Edit:

I added in with 'user_' and 'group_' the option of 'msrp_' but got backhanded.
SQL ERROR [ mysql4 ]

Unknown column 'msrp_' in 'field list' [1054]

SQL

UPDATE station_area52main.phpbb_users SET group_id = 5, msrp_ = NULL WHERE user_id = 2
Bah! This must mean something else is being set within it to allow for it to use user_colour as a full line, but if I add that it just curls up and dies.


Edit2:

AHA!

I replaced msrp_ with msrp_colour, and now I get a different error!
SQL ERROR [ mysql4 ]

Column 'msrp_colour' cannot be null [1048]

SQL

UPDATE station_area52main.phpbb_users SET group_id = 5, msrp_colour = NULL WHERE user_id = 2
Now I have made some progress I think! Just need to figure out this null thing, but I have a hunch my answer is at the top of functions_user.php file. Might or might not.

Edit3:

I'm about to deathstar this g-- d--- thing... :evil:

Edit4:

Code: Select all

'user_colour'			=> ' ',
I replaced that with

Code: Select all

'msrp_colour'			=> ' ',
But I am still getting that error. Roar.

Edit5: It has nothing to do with that I see. Its not putting a value in where the colour should be.

F**k.

----------------------------------------------------

Edit 6 - the HOLY****************** edition.

So, in the line

$sql_ary[str_replace('group_', 'user_', $attribute)] = $group_attributes[$attribute];

I tried adding

$sql_ary[str_replace('group_', 'user_', 'msrp_', $attribute)] = $group_attributes[$attribute];

and it was a no go.

So I removed user_

$sql_ary[str_replace('group_', 'msrp_', $attribute)] = $group_attributes[$attribute];

And... it... works...?

Wha!?

Can anyone confirm this that it is indeed doing what it should? Its setting colors, and everything now. No errors. :shock:

Wow. Just... lulz wow. All that work, and it was just that? Really? Seriously? *mind explosion*
PHPBB since 03
Jackuul Empire-MSRP V

Locked

Return to “[3.0.x] MOD Writers Discussion”