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 (http://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 :D

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 :D

[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]

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 :D

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 :D

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]

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 1347775503 at 1347775503 ---------------

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 :D