while loop deleting fields? help!! :)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

while loop deleting fields? help!! :)

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post 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?
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post 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)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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)?
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

hey i replaced them with the spam words, but it doesnt do anything... hhmmmm
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

anyone?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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;
}
?>
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post 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
Post Reply