Delete Spam via PHPMY ADMIN

Question

Hi,

Want to kill the spammers, even after the captche they people are continusly spaming using bots.

I am not so technical person, but aware little about PHP

 

Can someone plz help me with MYSQL queay, I need to delete all the question haveing even a single link and also delete the associate users.

 

Its really not possible to delete via admin panel.

 

Waiting for reply.

Thansk in advance.

Comments

+1
I am developing total database management plugin.
http://www.question2answer.org/qa/40782

I want to hear one thing to you.
What is "the question having even a single link"?

Answer 1

You can delete the users from qa_users table . 

 

If you want to stop spam , you could try out this plugin - http://www.question2answer.org/qa/37749/q2a-logical-captcha 

 

PS - This plugin only supports only English 

 

Thanks 

Comments

Hi,
Thanks for the reply,

I want to delete all the questions and comment which are having even a single link.

If possible then can you plz tell me the MYSQL queary

Thanks in advance

---

for the query , check the @pupi answer below :) . Good luck

Answer 2

That is not a good idea. Questions might have children posts (such as comments or answers). If you remove the questions the children will be orphaned. There will also be users who just posted a link to a site as a reference, and you will be removing them too. That is not good at all.

I will not add that delete statement as it is dangerous for you and someone who happens to find my post. However, I can ease your work by giving you a SQL statement with the posts and users who made them. Then you can evauluate if it is spam or not and navigate to the user (using the username in the results) and remove them manually.

I'm assuming a link is a piece of text that has HTTP inside it. You can easily change the statement to whatever you consider is a link... maybe a piece of text with WWW? Here is the SQL statement to fetch that information:

SELECT postid, `type`, u.handle username, title, content FROM qa_posts p

JOIN qa_users u ON p.userid = u.userid 

WHERE LOWER(content) LIKE '%http%' OR LOWER(title) LIKE '%http%'

Comments

Thanks for the answer . I was about to post this query :) .. +1 for this nice descriptive answer .