Hi,
My SQL skills have hit a wall and i ask for somebody's help. This is a once-only batch conversion as I am moving from the old "Join Reason" mod to the new "Advanced Profile Fields" and would like to copy the table data from the old mod to the new field in their profile:
I have this query that i run:
use smf209; select smf209_members.id_member, smf209_members.member_name, smf209_members.join_reason, smf209_themes.variable, smf209_themes.value FROM smf209_themes INNER JOIN smf209_members ON smf209_members.id_member=smf209_themes.id_member WHERE smf209_themes.variable='cust_joinre' AND smf209_members.join_reason !='';
and it produces this:
+-----------+-------------+-------------+-------------+-------------+
| id_member | member_name | join_reason | variable | value |
+-----------+-------------+-------------+-------------+-------------+
| 3113 | test | MOD-REASON | cust_joinre | SMF-REASON |
| 3114 | test2 | MOD-REASON2 | cust_joinre | SMF-REASON2 |
+-----------+-------------+-------------+-------------+-------------+
BUT i can't figure out how to code the SQL to update the themes table row for cust_joinre from join_reason
If you can help then please let me know.
OK, i worked that one out with help from google:
UPDATE smf209_themes
JOIN smf209_members ON smf209_members.id_member=smf209_themes.id_member
SET smf209_themes.value = smf209_members.join_reason
WHERE smf209_themes.variable='cust_joinre' AND smf209_members.join_reason !='';
but i realise now that I need to add a row to the themes table (looks like this) with the "variable" column set to 'cust_joinre' for each member with something in the join_reason;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id_member | mediumint(8) | NO | PRI | 0 | |
| id_theme | tinyint(4) unsigned | NO | PRI | 1 | |
| variable | varchar(255) | NO | PRI | | |
| value | mediumtext | NO | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
not sure how though ?
figured it out and run via a script - it may help someone someday ...
#!/bin/bash
rm jrf
echo "use smf209; SELECT smf209_members.id_member, smf209_members.join_reason FROM smf209_members WHERE smf209_members.join_reason !='';" | mysql -uroot -proot > jrf
count=0
while read a b; do
let count=count+1
if [ $count -gt 1 ]; then
echo "use smf209; REPLACE INTO smf209_themes(variable, value, id_member) VALUES (SUBSTRING('cust_joinre', 1, 255), SUBSTRING('unset yet', 1, 65534), $a);" | mysql -uroot -proot
fi
done < jrf
echo "USE smf209; \
UPDATE smf209_themes \
JOIN smf209_members ON smf209_members.id_member=smf209_themes.id_member \
SET smf209_themes.value = smf209_members.join_reason \
WHERE smf209_themes.variable='cust_joinre' AND smf209_members.join_reason !='';" | mysql -uroot -p****
i really hope the password i just removed from that code is not the password you use as it is really insecure. also you should not use the root account as the mysql user. you should assign a user specific for SMF and use that one to run a script like this.
thanks for looking out - that's my dev system password anyway.
appreciated.
i am the only person who (hopefully) will ever have access to the command line so i am a bit lax about doing it right, as you suggest. I do have many layers making access to the command line difficult though.