Query to Exclude Certain Results

[datoshway]

I have a table with a comments column.  Some of those comments contain bad words.  How can I exclude the whole row if these certain words exist in the comments column?  Here is my query before the modification i'm talking about above.

$strQuery				= "SELECT * FROM Comments ORDER BY DATE_FORMAT( `dtAdded`, '%B%d,%Y' ) DESC LIMIT 3";
$queryGetComments			= db_query($strQuery);

 

 

[osyrys14]

You could try selecting only the rows you want instead of selecting * from the table... That's one way.

[datoshway]

Well how would that solve the issue of eliminating results that have certain key words in it?

 

 

[fugix]

You could create an array of all the bad words that you want excluded. The do something like

"select * from table_name where column_name != array_name order by column_name desc

[osyrys14]

My apologies, I read that as how to select and exclude the comments column all together. 

[fugix]

My apologies, I read that as how to select and exclude the comments column all together. 

no problem 

[datoshway]

So I tried the code posted above and it doesn't seem to be working.  This is my full query.

$Bad_Words = "crap";

	$strQuery				= "select * from Comments where Message != Bad_Words order by dtAdded desc";
	$queryGetComments		= db_query($strQuery);

 

 

Any ideas?

[fugix]

use

$Bad_Words = "crap";

	$strQuery	 = "select * from Comments where Message != $Bad_Words order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

 

and let me know what you get

[datoshway]

This is what comes back:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /xxx/_database.php on line 39

 

[fugix]

show me your full sql code please

[datoshway]

Ok here it is:

 

PHP

<?php
				if (db_num_rows($queryGetComments) > 0) {
					?>
        <?php
                                while ($objRow = db_fetch_object($queryGetComments)) {
								$intArticleID	= intval($objRow->intID);
                                    $intID	= stripslashes($objRow->intID);
                                    $Message	= stripslashes($objRow->Message);
                                    $Category	= stripslashes($objRow->Category);
								$dtAdded	= stripslashes($objRow->dtAdded);

                                     db_free_result($queryGetArticle);


							?> 
  <?php
                                    if (strlen($Message) > 252) {
                                        $Message	= substr($Message, 0, 252) . " ";
                                    }
                                    ?>
                                    
<?php echo $Message; ?>
...<a href='detail.php?id=<?php echo $intArticleID; ?>'>read full email</a>
     
  
<?php
                                }
                                
                                db_free_result($queryGetComments);
						?>                  
                        <?php
                } else {
                    ?>
<div id="errornoproducts">No emails at this time. Check back soon!</div>
<?php
                }
            ?>

 

 

Controller File

<?php

define("STR_RELATIVE_PATH", "");
define("STR_CURRENT_SECTION", "home");

require STR_RELATIVE_PATH . "_includes/_common_data.php";
require STR_RELATIVE_PATH . "_includes/_database.php";

db_connect();
db_select();

$Bad_Words = "crap";

	$strQuery	 = "select * from Comments where Message != $Bad_Words order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

?>

 

Thanks in advance!

 

 

[fugix]

if (db_num_rows($queryGetComments) > 0) {

 

where is db_num_rows() coming from...

[datoshway]

I'm not sure, I got some help with this query.  How would you suggest setting it up?

 

[datoshway]

OK I think I might have been wrong about that error I went back to my original code with your last suggestion and i'm getting this error.

 

 

Unknown column 'crap' in 'where clause'

 

With this being the query.

$Bad_Words = "crap";

	$strQuery	 = "select * from Comments where Message != $Bad_Words order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

 

[fugix]

okay i think that it might be because i forgot to put quotes around the variable...try this

$Bad_Words = "crap";

	$strQuery	 = "select * from Comments where Message != '$Bad_Words' order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

[datoshway]

That eliminated the error but now i'm still getting results with the word crap in them...

[fugix]

im guessing that your message has multiple words..in this case it will still show everything....lets say your message is something like.  "Test Message, crap".....that entire message does not equal "crap" even though it has the word in it...make sense?

[datoshway]

Yes the message does have multiple words, so how can we eliminate the result if it has one of those words?

 

 

[fugix]

try

$strQuery = "select * from Comments where Message LIKE '%$Bad_Words%' order by dtAdded desc";

[datoshway]

Well it did something but not what we need... That only included results with crap in them instead of eliminating results with crap in them..

[datoshway]

Ok got it, we are supposed to be used NOT LIKE instead of LIKE.  Your last suggestion was close.  So the query looks like this now and it works.

 

$Bad_Words = "crap";

	$strQuery = "select * from Comments where Message NOT LIKE '%$Bad_Words%' order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

 

Thanks for all your help.

 

[datoshway]

Ok so I thought I had it but not so much.  It works with just one word (crap), if I add more then one word (crap,suck) then it doesn't work.  Any ideas??  Here is the full query..

$Bad_Words = "crap";

	$strQuery = "select * from Comments where Message NOT LIKE '%$Bad_Words%' order by dtAdded desc";
	$queryGetComments = mysql_query($strQuery) or die(mysql_error());

 

 

[fugix]

i found this code from a website...looks to be what you need...will need to change the query to NOT LIKE and the var values

$keywords= array("big","blue","sky");
$keycount= count($keywords);
for ($i=0; $i<$keycount; $i++)
{
$sql = "select * from ` domains ` where ` keyword ` like ' % " . $keywords [ $i ] . " % ' ";
$list = mysql_query($sql);
while ($lst = mysql_fetch_array($list))
{ ... }
mysql_free_result($list);
}

[mikosiko]

a quick/dirty solution:

 

don't do anything in your select... just get the recordset and pos-process it in php... in your case something like this:

 

  $badwords = array('/ bad1 /', 
                    '/ bad2 /', 
                    '/ bad3 /');  // this is the array of your bad-words ... could be a separate file for better maintenance

   $replace_with = ' ***** ';  // if you need to write something instead of the bad-words.

   $strQuery = "SELECT * FROM $table ORDER BY dtAdded DESC";  // replace $table with your table name
   $queryGetComments = mysql_query($strQuery) or die(mysql_error());

  // add any validation that you need

  while ($row = mysql_fetch_assoc($queryGetComments) {
      // anything you need here...
      echo preg_replace($badwords, $replace_with , $row['comment-column']);  // replace 'comment-column' with your comment column.. using echo only for testing purposes.
  }

[datoshway]

This looks like it's going to simply replace the words, all I need to do is exclude the whole result (row) if any of those words exist in the Message Column..