I'm working on converting my Invision 2.0.0.4 forum to SMF 1.1.2
I'd like to hack some extra features into the converter to carry over custom member settings. For example, I'd like to take the values from IBP's custom profile fields and migrate them.
I'm still learning how the converter works, and I'm not a PHP/SQL expert.
In the "Converting Members" section of invision2_to_smf.sql,
I've added another LEFT JOIN to call data from the ibf_pfields_content table as pf:
FROM {$from_prefix}members AS m
LEFT JOIN {$from_prefix}member_extra AS me ON (m.id = me.id)
LEFT JOIN {$from_prefix}members_converge AS mc ON (m.id = mc.converge_id)
LEFT JOIN {$from_prefix}pfields_content AS pf (m.id = pf.member_id)
WHERE m.id != 0;
Was I correct in using a LEFT JOIN to do this?
Then I want to "select pf.field_1 AS gender" (which would be added to the select array directly above this).
My problem is that I need to convert the values (not just copy them over), because the built-in SMF gender field interprets the values differently. How do I add an IF statement to cover this conversion:
if "pf.field_1" is null, send the value 0 to gender
if "pf.field_1" == "m", send the value 1 to gender
if "pf.field_1" == "f", send the value 2 to gender
I see something similar being done on line 57:
IF(m.mgroup = 4, 1, IF(m.mgroup > 5, m.mgroup + 3, 0)) AS ID_GROUP,
But I'm not sure how to implement it.
Try
CASE pf.field_1
WHEN 'm' THEN '1'
WHEN 'f' THEN '2'
ELSE '0'
END AS gender