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