need help with a (My)SQL statement to update a new profile field

Started by aegersz, January 24, 2018, 09:08:29 AM

Previous topic - Next topic

aegersz

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.
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

aegersz

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 ?
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

aegersz

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****
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

Illori

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.

aegersz

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.
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

Advertisement: