mysql update row if exists

Question

quick mysql php question.

i have a a $row['sentence'] and another $row['rank']

i'm tryin to write a thing..that says..if you find ...these certain words in the $row['sentence'].. insert or update the $row['rank'] by anything i specify..

ex. this sentence has badwords.. so the rank row..will become 1.

ex2. this sentence is clean so the rank row will become 5

thanks..if i confused u ..tell..me i will try another way..

Answer 1

Try this:

UPDATE myTable SET rank = 1 WHERE sentence LIKE "%bad words%";
UPDATE myTable SET rank = 5 WHERE sentence NOT LIKE "%bad words%";

That's a little simplistic obviously, and not even very efficient - it'd be quicker to set everything to rank - 5 and then change back just the ones you need. Anyway, I hope that's what you were after.

Answer 2

You can create a table with each bad word as a record and then parse through it to create a larger sql statement. This way, you can add onto the list of bad words, whatever they might be from the database and not have to manually enter each one for each like clause.

So for example:

$some_array = array(<list of bad words retrieved from db>);

$query = "update mytable set rank = 1 where ";

foreach ($some_array as $item) {
  $query .= "sentence like '%$item%'and ";
}

//remove last "and" from end of $query string
echo $query;

So it will generate a sql statement like what nickf provided but with a long list of all the bad words.

update mytable set rank = 1 where sentence like '%bad word%' and sentence like ...

Also not efficient but it should accomplish what you need to some degree.