[SOLVED] Changing 'X' in table 'Y' when 'P' in table 'Q' equals 'T'

[Bisa]

Dunno if you understood the subject correctly but what I want to do is run a cron once a week to sync my Forum with another application I'm running. vBulletin which I'm using helps me set up the cron I just need a php file with the actual task and thus I turn to you guys here for help =)

 

Basically if usergroupid in the table forum_users is 1 I want to change whatever value adminaccess has in table app_users. (The tables are both in the same db but used by two diff apps).

[JasonLewis]

Hm... I've been muddling your question over. My SQL isn't brilliant, but I just can't seem to think of a way to accomplish this. I mean, when you do an UPDATE query you UPDATE table SET field='value' WHERE another_field_in_same_table='a certain value'.

Gah, I'm confusing myself...

[Mchl]

Could you show us details of those two tables? You need to have some relation between them if you want it done on one query

 

UPDATE forum_users AS fu, app_users AS au SET au.adminaccess = 'whatever' WHERE (au.userID = fu.userID) AND (fu.usergroupid = 1)

 

this is what I've come up with, with what data you posted. I don't know how records in both tables are related, so I did it just like that

(au.userID = fu.userID)

 

 

[Bisa]

Well, instead of trying to make general examples I'll go with the full story 

 

I'm using eqdkp and vBulletin, I've managed to bridge the two applications to allow users to log in with one acc on them both. In eqdkp users are able to create characters/toons and by default these toons do not get a rank, this rank is needed for eqdkp to do various automated tasks and I would like to check whether or not the forum_users(username) is a member of the user group "15", if they are the eqdkp_members(member_name) that is identical to (username) should have there (rank_id) edited into "2".

 

dunno if that made things clearer?  :-\

 

Edit* This might fit better into the php help section seeing the real problem might be getting the "while"/"ifs" and "elses" to work properly?

[Mchl]

So...

 

UPDATE forum_users AS fu, eqdkp_members AS em SET em.rank_id =2 WHERE em.member_name = fu.username AND fu.group = 15

 

... Don;t know vBulletin database structure. Perhaps group memberships are stored in yet another table... Nevertheless it should be possible to do it in one query.

[Bisa]

So...

 

UPDATE forum_users AS fu, eqdkp_members AS em SET em.rank_id =2 WHERE em.member_name = fu.username AND fu.group = 15

 

... Don;t know vBulletin database structure. Perhaps group memberships are stored in yet another table... Nevertheless it should be possible to do it in one query.

 

ah! Cheers, this worked like a charm thank you =)

 

another thing, if I aso wanted to include the groups 10 and 11 in addition to 15 can I use

fu.usergroupid = 10 OR fu.usergroupid = 11

or will this break the check and simply change the rank on all members in grp 11 without checking the usernames?

[Mchl]

UPDATE forum_users AS fu, eqdkp_members AS em SET em.rank_id =2 WHERE em.member_name = fu.username AND fu.group IN (10,11,15)

[Bisa]

Lovely, you saved my back, the moderators were starting to whine about having to do to many small repetitive tasks already