Easy peasy query stuff

Ok... so I'm sure this is MySQL 101 stuff, but I think I just don't know what to type into the search engines to get the answers I'm looking for.

Ok... here's the sitch. I've got a ton of spam bot users in my user database. I want to get rid of them. I want to do searches for things that are tell tale signs. So for example, I can do this:

SELECT * FROM user WHERE email like '%.ru'

and then

DELETE FROM user WHERE email like '%.ru'

after I take a look and make sure there are no legit users amongst them.

The problem is that I also need it to delete those same corresponding userIDs from user_address and user_phone.

So how do I put a little query or delete line in PHP MyAdmin's SQL box to make this happen?

Part two....
Could I just delete from the one table 'user' and then enter some sort of command that would basically say... "Check the user table and look at all the userIDs in it. Now compare those userIDs with the userIDs in the user_address and user_phone tables. Now delete any rows that have userIDs that are not in the user table." Is that possible? Did that even make sense?


Part three....
I also have a phpBB forum on the site. It uses tables in a totally different database. I think the only shared field is the email... it doesn't use the same userID field I don't think. And there area bunch of tables associated with the forum. I suppose I would need to delete out of all of them. Any suggestions there?

 

You should be able to just add multiple tables to your DELETE query:

DELETE FROM user, user_address, user_phone WHERE email LIKE '%.ru';

The problem is if the column 'email' is in user_address and user_phone.

Do you have 3 seperate tables? User, User_Address and User_Phone?

Or is User the table name and Email, User_Address and User_Phone all columns?

 

Part 1/Part 2

DELETE from user_address where userid in (SELECT userid from user where email like '%.ru')

DELETE from user_phone where userid in (SELECT userid from user where email like '%.ru')

DELETE from user where email like '%.ru'

Will do it providing userid is the foreign key in the other two tables.

Satisfy yourself first though by trying this in your SQL admin box...
SELECT * from user_address where userid in (SELECT userid from user where email like '%.ru')
SELECT * from user_phone where userid in (SELECT userid from user where email like '%.ru')

just to confirm you will be deleting the right records!

Part 3...
It is possible to query over multiple databases but its complex so instead I would first of all store the email addresses you want to delete....
Select email from user where email like '%.ru';

Having got the email addresses, export them so you have a list. Typically I'd export them into a csv file and then open in Excel. There you can use excel concatenation to turn...



:


into....

'emailaddress1@somewhere.ru',
'emailaddress2@somewhere.ru',
:
'emailaddressN@somewhere.ru',

which gives you everything you need to be able to run the following on your other database...

DELETE FROM <table> where <emailcolumnname> IN (
'emailaddress1@somewhere.ru',
'emailaddress2@somewhere.ru',
:
'emailaddressN@somewhere.ru'
)

Obviously, you'd need to do part 3 before part 1/2!

Of course, if you're going to be doing this regularly, it would be far better to create a php script to do it!

 

Just realized I never said thank you. Thanks to both of you. Especially you, eccentricNick!!

 

Oh... and I think the most elegant way to do Part 2 is something like this:

delete from user_address where userid not in (SELECT recid from user)
delete from user_phone where userid not in (SELECT recid from user)