The spam bots who sign up spam members do not have a photo eventhough it is a requirement.
A good way to filter them out would be to delete all member with no photo.
This could be done with a MYSQL statement.
The database seems complicated.
Does anyone know how to do a SELECT all users with no photo query?
We build. You grow.
Get best community software hereStart a social network, a fan-site, an education project with oxwall - free opensource community software
Delete all members with no profile picture | Forum
Leader dave
Jun 1 '13
if it is the avatar, i believe it is stored in ow_base_avatar. Without testing it i would say you might want to select id from ow_base_user and inner join ow_base_avatar on userId then you can weed them out as you go thru the while loop.. As i said i have not tested this as i am in the middle of a conversion myself.
The idea being if they are in the user table but not in the avatar table then they are prob spammers. Back both your tables or your whole db first before you remove anything.
The Forum post is edited by dave Jun 1 '13
Leader dave
Jun 1 '13
Thanks paul, i was not sure if left or inner would do it, but im in the middle of xfering all my data from the other site which i need to convert the data to oxwall format and as you know its very detailed work that you have to give your full attention, so i really didnt have time to test and layout the query... Thanks buddy for helping him :)
John
Jun 1 '13
If you use a SELECT to find the users with no avatars it works.
This is the query.
I find 320.
If you try to do that as DELETE
Like this
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN ow_base_avatar ON ow_base_user.id = ow_base_avatar.userId WHERE ' at line 3
This is the query.
SELECT * FROM `ow_base_user`
LEFT JOIN ow_base_avatar
ON ow_base_user.id = ow_base_avatar.userId
WHERE ow_base_avatar.userId IS NULL
I find 320.
If you try to do that as DELETE
Like this
DELETE FROM `ow_base_user`
LEFT JOIN ow_base_avatar
ON ow_base_user.id = ow_base_avatar.userId
WHERE ow_base_avatar.userId IS NULL
You get this error message:
This is in PHPmyadmin
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN ow_base_avatar ON ow_base_user.id = ow_base_avatar.userId WHERE ' at line 3
Leader dave
Jun 1 '13
i wonder also if inner would work as well
DELETE ow_base_user.* FROM ow_base_user
INNER JOIN ow_base_avatar ON (ow_base_user.id = ow_base_avatar.userId)
WHERE ow_base_avatar.userId IS NULL
remember this will not remove the from any tables, lets hope they are just in the user table, but they may also be in other tables like the user role table and others.
The Forum post is edited by dave Jun 1 '13
Team
Purusothaman Ramanujam
Jun 3 '13
Dave,
This SQL will not delete all the content posted by those users as the data will be across several tables.
This SQL will not delete all the content posted by those users as the data will be across several tables.
Leader dave
Jun 3 '13
Dave,
This SQL will not delete all the content posted by those users as the data will be across several tables.
Right :) i did tell him that in my last post. Thats good that you restated that so he knows for sure knows that.. Especially with my typos on that statment it was not clear... :)
The Forum post is edited by dave Jun 3 '13
