[Forum] MySQL query help. How to change usergroupid for users with 0 posts AND 1 more thing?

[biggazillakilla]

Background: I want to ban all members who have a URL in their profile and have 0 posts. So I am trying to change the usergroupid of all members who meet two criteria.

I'm new to MySQL, so I'm not sure how to query two different tables. I've tried various combinations, but none seem to work.

I've tried two queries, neither of which worked. Here they are:

UPDATE
    user
SET
    usergroupid = 8
WHERE
    `user`.`posts` = '0',`userfield`.`field1` = 'www.example.com'

No dice.

UPDATE
    user
SET
    usergroupid = 8
WHERE
    `user`.`posts` = '0'
AND
    `userfield`.`field1` = 'www.example.com'

Also didn't work.

Can you let me know where I'm going wrong? Or what terms to search for?

Thanks!

(Note: I'm changing a bit of information in the above, but the idea is the same.)

[Wayne Luke]

The Homepage URL is stored in the user table in the homepage field. The userfield table stores Custom User Profile Fields. The default for field1 is the Biography field.

UPDATE user SET usergroupid = 8 WHERE posts = 0 AND LENGTH(homepage) > 0;

[biggazillakilla]

The Homepage URL is stored in the user table in the homepage field. The userfield table stores Custom User Profile Fields. The default for field1 is the Biography field.

UPDATE user SET usergroupid = 8 WHERE posts = 0 AND LENGTH(homepage) > 0;

Thanks, Wayne. I actually want to search for a value that appears in field1 (there are almost 8,000 users with this particular value). I just didn't want to post it here because this value makes it easy to find the people who are using that well-known forum spamming software that begins with an X.

So my question is how do I change the usergroup based on two criteria that appear in different tables?

Thanks again.

[Wayne Luke]

UPDATE user SET usergroupid = 8   
  WHERE user.userid IN(SELECT uf.userid FROM userfield as uf WHERE uf.field1 = 'XXXXX');

[biggazillakilla]

UPDATE user SET usergroupid = 8   
  WHERE user.userid IN(SELECT uf.userid FROM userfield as uf WHERE uf.field1 = 'XXXXX');

Wayne, in the above code does uf mean userfield ?

For example, does this:

SELECT uf.userid FROM userfield as uf

mean:

SELECT userfield.userid FROM userfield as userfield

?

Thanks again.

[Wayne Luke]

Yes... Just a way of shortening things overall.

[r5e]

I am also wishing to clean up my database with mySQL queries as sadly the pruning options do not give enough scope to delete/move users based on userfield criteria (though I have already got thousands by the simple criteria offered there). I have also trawled vbulletin.org for mods, finding some excellent spam moderation tools, but nothing for cleaning old users out that can do the job. I have 2 common scenarios as follows:

1. username matches field5 & field3 and field1 contains only "man".
2. most of the userfields simply have "1" in them.

These are only found in usergroup 2 (newly registered) or usergroup 8 (banned). These are the 2 most common attacks.

Additionally, I'd like to delete users from group 2 who have posted links in their profile fields who have 1 or less posts (profile spammers).

I am also concerned that simply deleting users via a query like this will not properly remove any stray unnoticed damage they may have left behind, such as visitor messages, posts, events, private messages, etc. Also, while I can navigate my way around phpMyAdmin and I understand the concept of running a query, I have never before run my own and I am nervous about running a cut&paste script that someone else posts up for me on my live database. Yes, of course I would back it up first, but it would be good to know if there are some useful suggested commands to simply display the results of what the query would run to make sure that I have got the criteria right before I start zapping.

Sorry if my questions are convoluted, but any help would be appreciated (at least an example of a query I could run for the above 2 scenarios and a way to test by display first before committing).

[BirdOPrey5]

The OP was not deleting users- you cannot delete users via simple queries.

[r5e]

The OP was not deleting users- you cannot delete users via simple queries.

Thanks and understood. A better way would be to change the usergroup and I can then run a prune operation on them from the adminCP. So the question remains: What is the best way to do this via a query with the above criteria?

[BirdOPrey5]

Thanks and understood. A better way would be to change the usergroup and I can then run a prune operation on them from the adminCP. So the question remains: What is the best way to do this via a query with the above criteria?

First I would make a new usergroup, make it a "banned" group. For this example lets assume it is usergroupid 10.

UPDATE user SET usergroupid = 10
  WHERE user.userid IN(SELECT uf.userid FROM userfield as uf WHERE uf.field1 = '1' OR uf.field2 = '1' OR uf.field3 = '1');

This will move everyone who has a 1 for any of their first 3 userfields to usergroup 10. No one should really have a 1 for any of these fields.

As for the other condition-

UPDATE user SET usergroupid = 10
  WHERE user.userid IN(SELECT uf.userid FROM userfield as uf WHERE uf.field5 = user.username AND uf.field1 = 'man');

That should do the same, I'm not worrying about field 3- if field 5 is the username and field 1 is man I think that's a good bet it's a spammer.

Now you'll have all these accounts in usergrouop 10, you can prune via the built in tools.

BACKUP YOUR DATABASE BEFORE ATTEMPTING MANUAL QUERIES LIKE THIS.

[r5e]

That's very helpful, thanks. I'll back up my database and have a play with these queries. Cheers.

[biggazillakilla]

Hi, all. I'm still in the process of getting my test server set up properly to try this queries out on the test database.

In the meantime, I was hoping for a clarification--I basically want to find users with userfield.field1 = 'man' AND `user`.`posts` = '0'.

In other words, I want to move the users to the banned group, but only if they have a post count of 0. This has been the challenge for me since I'm not sure how to run queries on different tables at once. Yes, I'm still learning... =/

Thanks again!

[BirdOPrey5]

post count is stored in the user table, as user.posts so try:

UPDATE user SET usergroupid = 10
  WHERE user.userid IN(SELECT uf.userid FROM userfield as uf WHERE uf.field1 = 'man') AND user.posts = 0;

BACKUP YOUR DATABASE BEFORE ATTEMPTING MANUAL QUERIES LIKE THIS.