query boolean mode problem

nuil · February 5, 2013

Im unable to get the correct results from mysql using boolean mode, i have MyISAM db with FULLTEXT

Read up here what to expect from the results and mine are different.

Exact match works

"two words"

Single word works

words

But these queries wont, only the last word seems to be what makes the rusults happen, correct data to return results exist in the db

-two +words <-- should be words and not two, produces results on words and two

+two -words <-- should be two and not words, produces 0 results

Its probably my code, but i have tried so much i dont know why it keeps failing

Is there any other settings im missing for mysql db?

<?php
//											    /*
function customError($errno, $errstr, $error_file, $error_line) {
    echo "<br><b>Error:</b> [$errno] $errstr <br> $error_file $error_line <br>";
}
set_error_handler("customError");
//											    */
$dbusername="";
$dbuserpass="";
$dbhost="localhost";
$dbname7="TempTB";
$table1="CardsBW";
//''''''''''Create Table Database config''''''''''//
$column1="cardName";

mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname7) or die ('Cannot select database');

if(isset($_GET['q'])) {
   $search = strip_tags(urldecode(filter_var($_GET['q'], FILTER_SANITIZE_ENCODED)));
} else {
   $search = "";
}

$keywords = array($search);

if ($search == NULL or $search == '%'){
} else {
   for ($i=0; $i<count($keywords); $i++) {

       // Works but incorrect results
       //"SELECT * FROM ".$table1." WHERE MATCH ".$column1." AGAINST ('%".$keywords[$i]."%' IN BOOLEAN MODE)".
       $query = "SELECT * FROM ".$table1." WHERE MATCH" .
       " ".$column1." AGAINST ('.$keywords[$i].' IN BOOLEAN MODE)" .
       " ORDER BY ".$column1." ";

       echo $keywords[$i].'<br>';
   }
   $result = mysql_query($query) or die(mysql_error());
}

if ($search == NULL or $search == '%'){
} else {
   $count = mysql_num_rows($result);
}

echo '
<body onload="self.focus();document.searchform.search.focus()"><center><br>
<form name="searchform" method="GET" action="">
<input type="text" name="q" size="20" TABINDEX="1" placeholder="';  
foreach($keywords as $value) {
   print "$value";
} echo '" onfocus="if(this.value==\'Initial text\')this.value=\'" onblur="if(this.value==\')this.value=\'Initial text\'">
<input type="submit" value="Search">
</form>

<table id="search" bgcolor="#00BB77"><tr><td>';
$endtable = <<<EOT
</td><tr></table></center><br>
EOT;

if ($search == NULL){
   echo '<b> Please enter a search parameter to continue </b>'.$endtable;
} elseif ($search == '%'){
   echo '<b> Please enter a search parameter to continue </b>'.$endtable;
} elseif ($count <= 0){
   echo 'Your search for <b><FONT COLOR="0022FF">';
   foreach($keywords as $value) { print "$value "; }
   echo '</b></font> found <b>' . $count . ' cards</b>'.$endtable;
} else {
   echo 'Your search for <b><FONT COLOR="0022FF">';
   foreach($keywords as $value) { print "$value "; }
   echo '</b></font> found <b>' . $count . ' cards</b>'.$endtable;

while($row = mysql_fetch_array($result)) {
echo '
<h3 class="dynamic-din">'.$row[$column1].'</h3>';
   }
}
   //<img style="width: 102px; height: 142px;" class="cardImage" src="'.$row[$column2].'" alt="'.$row[$column1].'" height="142" width="102">';
echo '
</body>
</html>';

if ($search == NULL or $search == '%') {
} else {
   //clear memory
   mysql_free_result($result);
}

?>

Psycho · February 5, 2013

You need to first test your query in your database admin pane. Once it is getting the correct results - THEN implement it in the code. Trying to debug problems queries that are dynamically generated in PHP can be difficult when you don't know where the problem is.

But, I see one glaring problem in the above code. You are apparently running multiple queries in loops. Not only is this a very poor implementation, but it makes no sense how you've done it. In the loop you are running the query and assigning the result set of the query to $result - then you loop again running the next query. You never use the contents of $result until all the queries in the loop have been executed. So, you are only left with the result from the last query executed in the loop.

nuil · February 5, 2013

Thanks

I'll start again i think, i'll search for a better tutorial one for bolean seach, the one i followed is here for the above

Would this be better? Or i should find more tutorials? it wont work either

if(isset($_GET['q'])) {
   $search = $_GET['q'];
} else {
   $search = "";
}


$keywords = $_GET['q'];
$query = "SELECT * FROM ".$table7." WHERE MATCH ".$column1." AGAINST ('.$keywords.' IN BOOLEAN MODE) ORDER BY ".$column1." ";

//echo $keywords.'<br>';
$result = mysql_query($query) or die(mysql_error());

if ($search == NULL or $search == '%'){
} else {
   $count = mysql_num_rows($result);
}

Psycho · February 5, 2013

At first it looked like you were running a separate query for each word in the array, but I was mistaken on that. What you ARE doing is redefining the query for each word in the array. Then, after you iterate over each word in the array you run the query (which was created for the last word of the array). In other words, the query does not contain anything for the words before the last one.

The tutorial you linked to has the exact same problem. The code you posted above won't work because you are not sanitizing and preparing the values appropriately. Go find a better tutorial.

Sign In

query boolean mode problem

Recommended Posts

nuil

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

nuil

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information