SQL to remove spam guestbook comments

Hi. our website has had loads of spam on its guestbook page. I've just installed googles recaptcha to try and stop the spam :)

What i would like to do now is remove all the unapproved comments from the MySQL data base.
please can some one have a look at the database dump below and assist me on how i can automatically run some SQL to target the unapproved comments and delete them.

The first comment is approved and the last one is unapproved. It looks like the unapproved one has a 0 at the end so i guess that that is how the CMS knows its unapproved?

There is actually another 100 unapproved posts in this table but i am only showing a sample below:

thanks

DROP TABLE IF EXISTS btGuestBook;

CREATE TABLE IF NOT EXISTS `btGuestBook` (
  `bID` int(10) unsigned NOT NULL,
  `requireApproval` int(11) default '0',
  `title` varchar(100) default 'Comments',
  `dateFormat` varchar(100) default NULL,
  `displayGuestBookForm` int(11) default '1',
  `displayCaptcha` int(11) default '1',
  `authenticationRequired` int(11) default '0',
  `notifyEmail` varchar(100) default NULL,
  PRIMARY KEY  (`bID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO btGuestBook VALUES(34,1,'Tell us what you think','M jS, Y',1,1,0,'david@tseoc.co.uk')
 ,(31,1,'Testimonials','M jS, Y',1,1,0,'chris@tseoc.co.uk');

DROP TABLE IF EXISTS btGuestBookEntries;

CREATE TABLE IF NOT EXISTS `btGuestBookEntries` (
  `bID` int(11) default NULL,
  `cID` int(11) default '1',
  `entryID` int(11) NOT NULL auto_increment,
  `uID` int(11) default '0',
  `commentText` longtext,
  `user_name` varchar(100) default NULL,
  `user_email` varchar(100) default NULL,
  `entryDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `approved` int(11) default '1',
  PRIMARY KEY  (`entryID`),
  KEY `cID` (`cID`)
) ENGINE=MyISAM AUTO_INCREMENT=145 DEFAULT CHARSET=utf8;

INSERT INTO btGuestBookEntries VALUES(31,131,2,0,'Aaron Foxx Residential Lettings\r\n\r\nI am most impressed with the SEO Company. David and his team deliver a great service and have my website on the 1st page of Google for the most relevant search terms for my business. The SEO Company also designed and built my website and it is wonderful.  It is a core part of my business in generating income as well as being informative for my clients. I cannot recommend these guys enough. There are many SEO company’s out there but for me this one actually works. Thanks guys\r\nGavin Aaron Ellwood & Stuart Fox\r\nAaron Foxx Residential Lettings\r\nhttp://www.afrhomes.co.uk','Gavin Aaron Ellwood','gavin@afrhomes.co.uk','2013-09-01 18:09:43',1)
 ,(31,131,13,0,'http://www.rcbarnes.net/categories-26.html\r\ncvcbmkqidvxbjnwifmotzwshatrfascjobrcplqdhzokuphddwbmlqhwygofyjtbla\r\n¿¿\r\ntylvycpnkjomhypsxglqprrzwwpehpuktrcftsujbpvdidefrdwcrxgrwqoenrupfpjvqvmofeonfclpvnrynrgetfzxviwuoek\r\nhttp://www.rcbarnes.net/\r\n¿¿¿¿¿¿¿¿ ¿¿¿\r\n¿¿¿¿¿¿¿¿ ¿¿¿\r\nhttp://www.rcbarnes.net/categories-15.html\r\n¿¿¿¿¿¿ ¿¿¿¿¿\r\nrimcwerjxmmnvtvuiducmemnnbczigpqs\r\n¿¿¿ ¿¿¿¿¿¿','ReoffGaflal','calcymnbymn@hotmail.com','2013-10-06 08:35:46',0)

Open in new window

LVL 1
helpchrisplzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edtechdbaCommented:
If you need to immediately remove unapproved comments, how about setting up a trigger on your table to do that?

Here's a reference that may be helpful: http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

Simple example below
CREATE TRIGGER AfterInsert ON [dbo].[btGuestBookEntries] 
FOR INSERT
AS

DELETE btGuestBookEntries
WHERE approved = 0

GO

Open in new window


Or you may want to consider setting up a SQL Server Agent job to run a script to delete entries based on the approved value if you don't want to use a trigger.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
helpchrisplzAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.