Page 1 of 2

while loop deleting fields? help!! :)

Posted: Wed Jul 12, 2006 5:10 pm
by blade_922
im goin mad here, im kind of new to php, but know basics. Im a quick learner.

Ok in my site i get a loads of spam comments, you know.

so i want to write a script that searches the comments field and deletes any comment that contains specific words.

table is called mos_comment and field is comment

Here is what i have so far. im using the word "test" as an example of a comment that has this word and to delete that comment,

Code: Select all

<?php 
$total= mysql_query("Select COUNT(*) from mos_akocomment");  
?>

<?php

while ( $total<= 1 ) {

php mysql_query("DELETE FROM mos_akocomment WHERE comment = 'test'"); 

}

?>

Im not surprised its not working, anyone help me out :P

Posted: Wed Jul 12, 2006 5:18 pm
by RobertGonzalez

Code: Select all

<?php
$sql = 'UPDATE `mos_comments` SET `comments` = \'\' WHERE `comments` LIKE \'%' . $searchterm . '%\'';
?>
This will do searching for you without having to delete single lines through a loop. Just be careful, this could have serious issues in your app if your search term is common to other words.

Posted: Wed Jul 12, 2006 5:27 pm
by blade_922

Code: Select all

<?php 
$sql = 'UPDATE `mos_comments` SET `comments` = \'\' WHERE `comments` LIKE \'%' . $searchterm . '%\''; 
?>
Thanks. Ok so with that do i need to set what $searchterm is like $searchterm = test n what goes in that bit where comments` = \'\' comments` LIKE \'%' whats with the \ and % signs lol. and using that code how do i make it delete that comment?

Posted: Wed Jul 12, 2006 5:56 pm
by jamiel
% is like a wildcard in MySQL.

Code: Select all

DELETE FROM mos_comments WHERE comments LIKE '%$searchterm%';
.. is what you are looking for to delete. But I don't recommend using the above. Too ambigious and too easy to delete everything. You should rather be preventing the Spam at the submission level. eg. Block certain words from even entering your database.

Posted: Wed Jul 12, 2006 6:03 pm
by blade_922
yeh im going to do that. does the LIKE bit mean it searches for that exact keyword? what if that keyword is contained inside another word? or does it search for words that look similar to that word?

Posted: Wed Jul 12, 2006 6:05 pm
by RobertGonzalez
I agree with Jamiel when it comes to the rejecting of data at the entry level. But to clena what you have, you can use a version of the query I posted. What it is doing is editing every comment field in the moc_comments table and emptying it when there is data in that field that contains the term $searchterm. This is not an exact match, but a very loose cleaner that will literally remove the entire comment if any of the comment body contains even a semblance of $searchterm. Hence the warning to be careful.

Posted: Wed Jul 12, 2006 7:32 pm
by blade_922

Code: Select all

$searchterm = pharmacy;
k thats one word it searches for and deletes comment. How can i specify more than one word? (Put it in an array? guessed that)

Posted: Wed Jul 12, 2006 11:03 pm
by RobertGonzalez

Code: Select all

<?php
$searchterms = array('pharmacy', 'snots', 'booty', 'stinkbug', 'snaggletooth', 'footsmell', 'buttscratch', 'stupiduserentry');
$searchtermscount = count($searchterms);
if ($searchtermscount > 0)
{
    $searchstring = '';
    for ($i = 0; $i < $searchtermscount; $++)
    {
        $searchstring .= ( 0 == $i ) ? ' WHERE `comments` LIKE \'%' . $searchterms[$i] . '%\'' : ' OR `comments` LIKE \'%' . $searchterms[$i] . '%\'';
    }

    $sql = 'UPDATE `mos_comments` SET `comments` = \'\' ' . $searchstring;
}
?>
This is completely untested. Use/modify at your own risk.

Posted: Thu Jul 13, 2006 3:24 am
by Jenk
I'd recommend exporting the 'suspect' records to a different table, rather than deleting altogether. But then, how important are these records (not the spam records of course)?

Posted: Thu Jul 13, 2006 9:33 am
by blade_922
Everah wrote:

Code: Select all

<?php
$searchterms = array('pharmacy', 'snots', 'booty', 'stinkbug', 'snaggletooth', 'footsmell', 'buttscratch', 'stupiduserentry');
$searchtermscount = count($searchterms);
if ($searchtermscount > 0)
{
    $searchstring = '';
    for ($i = 0; $i < $searchtermscount; $++)
    {
        $searchstring .= ( 0 == $i ) ? ' WHERE `comments` LIKE \'%' . $searchterms[$i] . '%\'' : ' OR `comments` LIKE \'%' . $searchterms[$i] . '%\'';
    }

    $sql = 'UPDATE `mos_comments` SET `comments` = \'\' ' . $searchstring;
}
?>
This is completely untested. Use/modify at your own risk.


hhmmmm i tried that, it doesnt delete the comments. anyone knw if there is sumtin wrong with it.

Posted: Thu Jul 13, 2006 10:39 pm
by RobertGonzalez
Sorry, I should have told you to replace the array members in $searchterms with the words that you are looking for. My fault for not being clear enough.

Posted: Thu Jul 20, 2006 7:33 am
by blade_922
hey i replaced them with the spam words, but it doesnt do anything... hhmmmm

Posted: Fri Jul 21, 2006 12:15 pm
by blade_922
anyone?

Posted: Fri Jul 21, 2006 10:30 pm
by RobertGonzalez
Can you please echo out the $sql and post it.

Code: Select all

<?php
/**
 * Replace the terms in this array with the terms inside the comments you want to remove
 */
$searchterms = array('pharmacy', 'snots', 'booty', 'stinkbug', 'snaggletooth', 'footsmell', 'buttscratch', 'stupiduserentry');
$searchtermscount = count($searchterms);
if ($searchtermscount > 0)
{
    $searchstring = '';
    for ($i = 0; $i < $searchtermscount; $++)
    {
        $searchstring .= ( 0 == $i ) ? ' WHERE `comments` LIKE \'%' . $searchterms[$i] . '%\'' : ' OR `comments` LIKE \'%' . $searchterms[$i] . '%\'';
    }

    $sql = 'UPDATE `mos_comments` SET `comments` = \'\' ' . $searchstring;

    echo $sql;
}
?>

Posted: Sun Jul 23, 2006 5:52 pm
by blade_922
i get this with this code u just said in the above post

i hadda change this a bit coz u forgot to put $i++ u just put $++
[php $searchstring = '';
for ($i = 0; $i < $searchtermscount; $i++)




And i get this

UPDATE `mos_akocomment` SET `comment` = '' WHERE `comment` LIKE '%pharmacy%' OR `comment` LIKE '%snots%' OR `comment` LIKE '%booty%' OR `comment` LIKE '%stinkbug%' OR `comment` LIKE '%snaggletooth%' OR `comment` LIKE '%footsmell%' OR `comment` LIKE '%buttscratch%' OR `comment` LIKE '%stupiduserentry%'

my table is mos_akocomment and field is called comment. I changed that in the code to. n i get the above with the echo sql