vb.org Archive - Moving users into a user group based on signature url and ...

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - Moving users into a user group based on signature url and ... (https://vborg.vbsupport.ru/showthread.php?t=287852)

John Lester

09-15-2012 03:23 AM

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

1 Attachment(s)

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;

Code:

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	09-15-2012 12:33 PM

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:

Code:

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

09-15-2012 07:40 PM

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

09-15-2012 09:03 PM

Did you try this?

Code:

UPDATE user SET usergroupid = 3

WHERE (SELECT userid 

        FROM usertextfield

        WHERE signature LIKE '%google%')

AND posts = 0

AND lastvisit = 1342328885

kh99	09-15-2012 09:16 PM

Quote:

Originally Posted by John Lester (Post 2365757)

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.

Quote:

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

09-15-2012 11:17 PM

Quote:

Originally Posted by Scanu (Post 2365779)

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.

Code:

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

All times are GMT. The time now is 09:28 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01512 seconds Memory Usage 1,724KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: