Page 1 of 1

delete every occurence of a phrase

Posted: Fri Jul 26, 2002 10:58 am
by deezin
Hi. I know mysql but I don't know how to delete every occurence of a particular phrase. For example if the phrase "I love you" occurs somewhere is the description of a project how do I delete that phrase without deleting the actual record?

If I type in delte from projects where description like = 'I love you';

then the actual records will be deleted, but I want only the phrase itself, not the record to be deleted. Does anyone know how to do that? Do I have to do it in PHP or it possible to do it in MySQL?

Thanks for your help.

Posted: Fri Jul 26, 2002 11:00 am
by llimllib
how about:

Code: Select all

UPDATE projects SET description='' WHERE description like='I love you';

delete every occurence of I loveyou

Posted: Fri Jul 26, 2002 11:06 am
by deezin
Hi. Thank you for your quick reply. You said the answer was this:

UPDATE projects SET description='' WHERE description like='I love you';

Wouldn't this delete the rest of the description? For example the description might be "Blah blah blah, I love you, blah blah' and then we would lose the blah blahs, wouldn't we?

Thanks again.
Rachel

Posted: Fri Jul 26, 2002 12:30 pm
by llimllib
oh, thought you wanted anything with 'I love you' deleted. What you want to do is select all entries with 'i love you' in them, read them in a loop, run a str_replace on them, then update each one.

Posted: Fri Jul 26, 2002 3:55 pm
by fatalcure
uhm, just do:

Code: Select all

$query = "SELECT id, description FROM table";
$result = query_db($query);

while ($row = mysql_fetch_array($result)) {
     $id = $rowї"id"];
     $description = str_replace("I love you", "", $rowї"description"]);
     
     $updatequery = "UPDATE table SET description = '$description' WHERE id = $id";
     query_db($updatequery);
}
you could do something like that :)

Thank you

Posted: Fri Jul 26, 2002 6:55 pm
by deezin
:lol: :P

Thank you VERY much. This worked wonderfully. I really appreciate your kindess.

Rachel
AllFreelanceWork.com