Moving users into a user group based on signature url and ...

[John Lester]

Ok so I'm trying to move users from the registered user group into another user group if they have a certain url in their signature, have zero posts, and a certain lastvisit time.

This is the query I came up with;

UPDATE user SET usergroupid = 3
WHERE (SELECT userid 
        FROM usertextfield
        WHERE signature LIKE 'google')
AND posts = 0
AND lastvisit = 1342328885

The attached picture is what happens in phpmyadmin.

No users get moved Yes I have one user with the signature of www.google.com with zero posts and a lastvisit of 1341119285. Doesn't do anything different if I replace = with another symbol ( < > <= >= ) in the lastvisit clause.

[kh99]

I have to be honest and say that I don't fully understand your query, with a SELECT in there like that, so it that may be OK. But I think you want to use LIKE '%google%'. In any case, I probably would have done this:

UPDATE user LEFT JOIN usertextfield ON (user.userid = usertextfield.userid)
SET usergroupid = 3
WHERE signature LIKE '%google%'
AND posts = 0
AND lastvisit = 1342328885

[John Lester]

I got that select bit from reading over at stackoverflow, was pretty sure it wouldn't work but it didn't spit out an error either

Your suggestion did not work either I had tried using a JOIN (left and right) and neither worked, which is why I had nothing to lose by trying the query I posted

[Scanu]

Did you try this?

UPDATE user SET usergroupid = 3
WHERE (SELECT userid 
        FROM usertextfield
        WHERE signature LIKE '%google%')
AND posts = 0
AND lastvisit = 1342328885

[kh99]

Originally Posted by John Lester

I got that select bit from reading over at stackoverflow, was pretty sure it wouldn't work but it didn't spit out an error either

What I was trying to say above (except that I messed up that sentence and it doesn't make any sense) is that it may well be correct, I just don't know.

Your suggestion did not work either I had tried using a JOIN (left and right) and neither worked, which is why I had nothing to lose by trying the query I posted

Hmm...I tested mine before posting it and it seemed to work. I adjusted the WHERE stuff so that it would match one of my test users, but I don't think that should have made any difference.

[John Lester]

Originally Posted by Scanu

Did you try this?
<snip>

Hi I did indeed, the query executed successfully, but the users user group wasn't changed.

It might be an issue with the user, I'll have to adjust the lastactive time and see if that works.

--------------- Added [DATE]1347775503[/DATE] at [TIME]1347775503[/TIME] ---------------

So I decided I could live without using lastvisit since I'm already checking signature url and post count.

UPDATE user LEFT JOIN usertextfield ON (user.userid = usertextfield.userid)
SET usergroupid = 3
WHERE signature LIKE '%google%'
AND posts = 0

Funny enough, if I use the code I posted first and leave off the lastvisit time, it moves all users with zero posts regardless of what's in their signature. *shrug* I would love to go to school and learn the bass ackwards logic the codes seems to like