How to check words for occurence in database?

Question

I'd like to store banned words in a database to later determine if some word is present (hence being a banned word, which should be censored).

Word input form (ban.php) :

<form name="form" method="post" action="add.php">
    <input type="text" name="bad" id="bad">
    <input type="submit" name="submit" id="submit" size="12" value="submit">
</form>

PHP code (add.php) :

<?PHP
require_once("config.php"); // db conn

$bad = $_POST['bad'];
$bad = mysql_real_escape_string($bad);
$sql = "insert into my_table set bad='$bad'";

mysql_query($sql, $conn) or die(mysql_error());
echo "Done bad word added";
?>

Let's say we banned the word ugly. Now I want do this :

<?PHP
require_once("config.php"); // db conn

$qry    = "select * from my_table";
$result = mysql_query($qry) or die($qry);
$test   = "ugly"; // Example

if ($test == any of the words in the db my_table){
    echo "banned";
}else{
    echo "passed";
{
?>

How to do this? There are many added words in my_table :

id,bad (1,'ugly')
id,bad (2,'sick')
id,bad (3,'manal')
id,bad (4,'fog')

Robjong · Accepted Answer · 2012-02-26T12:22:02.467

4

You should attempt to select the word from the database, this is much faster than having to go through an array.

Something like this should work.

<?php
require_once("config.php"); // db conn

$test = "ugly"; // remember to use mysql_real_escape_string in the implementation

$qry = "SELECT * FROM `my_table` WHERE bad='{$test}'"; // select by value
$result=mysql_query($qry);

if( mysql_num_rows( $result ) ){ // we have a row with the 'bad' word
    echo "banned";
}else{
    echo "passed";
}
?>

edited Feb 26 '12 at 12:22

answered Feb 26 '12 at 11:37

Robjong

375
1
6

I don't see the point of a "select *"- you are pulling back unnecessary data from the query. A select count will yield a single int.. Which may not be a performance issue in a simple application such as this, but in a scaled application it could be – Lock Feb 26 '12 at 11:42
2

Correct, but that's one of the least problematic things here as I see it. From the code posted I figured the OP is quite new to PHP/MySQL, so I kept it simple and readable. – Robjong Feb 26 '12 at 11:49

score 3 · Answer 2 · answered Feb 26 '12 at 11:34

3

Use a different SQL statement

$qry = "select * from my_table where bad = '".$test."'";

Than just test the result if there is anything or nothing (banned or passed).

answered Feb 26 '12 at 11:34

user219882

15,274
23
93
138

score 2 · Answer 3 · answered Feb 26 '12 at 11:35

Your SQL of inserting does not make sense. You are inserting, but using the update syntax. Your query should be "insert into my_table values (1, '$bad');

In terms of looking for a banned word, you are better looking for the bad word through your query:

Select count(1) from my_table where word = banned word.

If you run mysql_num_rows over the query, anything greater than 0 means it is banned.

How to check words for occurence in database?

3 Answers3

Linked