As some of you know, I do many of the “webmaster” activities for the Michigan Mountain Biking Association. As part of this, I’m trying to do some MySQL stuff, but I’m really not sure where to start since it seems to be way beyond my abilities. So, I’m going to write it up here and hope that someone can help. Please? Help?
Thank you very much in advance for any help you can provide.
I’ll put it below the fold…
(Edited to add a bit more info after the first solution presented didn’t quite do what was needed.)
Our membership site runs aMember and the forum runs phpBB. I wish to have phpBB display each user’s membership status (from aMember) next to their name. To do this I feel it would be best to periodically look up each phpBB user up in the aMember DB by email address, and if that address belongs to an account which is an active member, change a value to something particular on the phpBB account. If the person is not a member (expired or non-existant) set the value to something else. It also needs to look into a blob of text to see if the email address is possibly there, since we’re also storing an alternate secondary email address so that people with family memberships can have two phpBB accounts with each listing as an active member.
I figure there’s one nice lump of SQL that’ll do it as a stored procedure which I could then just run with cron once a day.
Here’s the relevant phpBB info:
User’s ID in phpBB in phpbb_users table: phpbb_users.user_id: mediumint(8)
User’s Email in phpBB in phpbb_users table: phpbb_users.user_email: varchar(100)User’s ID in phpBB in profile_fields_data table: phpbb_profile_fields_data.user_id: mediumint(8)
Value to change in profile_fields_data table: phpbb_profile_fields_data.pf_mmba_member: tinyint(2)
Values for phpbb_profile_fields_data.pf_mmba_member:
NULL == Not Set (Should never be this)
1 == Yes (A member)
2 == No (Not a member)Due to how phpBB works rows are not present in phpbb_profile_fields_data for all possible phpbb_users.user_id values, so if these do not exist they should be created to ensure that all IDs have values. In short, rows are only created here either when the account is created (after the value is defined) or when the value is edited. I hope this is sufficient to describe the schema of that:
--
-- Table structure for table `phpbb_profile_fields_data`
--
CREATE TABLE `phpbb_profile_fields_data` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`pf_mmba_member` tinyint(2) default NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Here’s the aMember info:
User’s Email in aMember in amember_members table: amember_members.email: varchar(64)
User’s membership status in aMember in amember_members table: amember_members.status: smallint(6)Values for amember_member.status:
0 == Pending (Not a member)
1 == Active (Member)
2 == Expired (Not a member)Blob of text that contains the alternate/family member email address: amember_members.data: text
Example from amember_members.data:
a:7:{s:7:"bf_time";i:1228958937;s:8:"bf_value";d:1.40783106027276722203815250041014639492686910671181976795196533203125E-7;s:6:"status";a:2:{i:1;i:0;i:2;i:1;}s:9:"is_active";i:1;s:17:"signup_email_sent";i:1;s:9:"is_locked";s:0:"";s:19:"family_member_email";s:23:"daniellethall@gmail.com";}(Yes, I know this is a horrible way of storing data, but it’s what the product already does, and it’s best to not change the program itself.)
For reference, the schema for all the tables mentioned above can be found here: https://nuxx.net/files/mysql_help.tar.gz
update phpbb_profile_fields_data pfd, phpbb_users u
set pfd.pf_mmba_member=(
case (
exists (select 1 from amember_members amm where (amm.email = u.user_email or amm.email = pfd.pf_member_email_if_not_as_above) and amm.status = 1)
)
when true then 1 else 2 end
) where u.user_id = pfd.user_id;
Whoops, for some reason I thought the extra field went into phpbb, not aMember.
Strike the “or amm.email = pfd.pf_member_email_if_not_as_above” to go back to not-extra-credit mode and replace it with something like
or locate (u.user_email || ‘,’, amm.other_emails || ‘,’) > 0
Jonathan: Thanks very much. I actually used the first one as follows as a test, and it seemed to do what was needed without the extra credit:
For the extra credit stuff, are you meaning this? If so, I got an error “#1305 – FUNCTION dev_mmba_org.locate does not exist”:
I don’t have amm.other_emails created yet, but I think there’s a locate function that’s missing?
Hmm, I just found that the alternate email address will be found inside of a field of ‘text’ type, called amember_members.data. Unfortunately it’s munged in with a bunch of other stuff, so I think that we’ll have to just find it inside of there.
This is an example of what is contained in the amember_members.data field:
Hmm. I’m going to edit the original post a bit since I’ve learned a few more things about it all.
That appears to be input for PHP’s unserialize() function. At this point I’d advise switching horses and doing the usual select-fetch-update loop in PHP. Since mySQL barely does regexes, mySQL 4 doesn’t even try to do array types, and there’s no way mySQL will usefully index that blob, you’ll save yourself one table scan per phpBB user and a lot of aspirin by building a list of the emails and then stepping through all the phpBB users, doing an update query on their pf_mmba_member status where necessary.
Or, hack aMember.