search wont find whats in MySQL

  • barry
  • Graduate
  • Graduate
  • User avatar
  • Posts: 115
  • Loc: scotland

Post 3+ Months Ago

Hi all
I am having a dilema with a search system i am building.

I have thousands of listings in my database and the thing is when i search for a couple of things it returns no results even though i can clearly see the things in the database

Ok here is the problem

I search for take away using my search form .. the form istnt important .. its just a form

the form submits the $keywords to this


Code: [ Select ]
$keyword = $_GET['keyword'];
            
            
            $sql = "SELECT * FROM listing WHERE MATCH(companyname,type,description,address,keywords)
AGAINST('+$keyword* ~term' IN BOOLEAN MODE) ORDER BY keywords ASC " ;
                    
            $results = mysql_query($sql) or die("Query failed : " . mysql_error() . mysql_errno());
    
        }
    
     $tester = @mysql_num_rows($results);
if ($tester == 0) {echo "<p>Your search returns no results, please try again!!</p>
";} else {




echo'<p><strong>Your search for '.$keyword.' returned the results below:</strong><br />
<span class="style1">If you contact any of the companies below please mention Around Dundee as the place you found them!!</span><hr /></p>';

    $sql = "SELECT * FROM listing WHERE MATCH(companyname,type,address,shortdescription,description,keywords)
AGAINST('+$keyword* ~term *$keyword+ ~term' IN BOOLEAN MODE) ORDER BY RAND()" ;
                    
            $results = mysql_query($sql) or die("Query failed : " . mysql_error() . mysql_errno());
    
        
    
                while ($info = mysql_fetch_array($results))
                {
                
                echo"
            
                        <p class='thename'><b>Name: ".$info['companyname']."</b></p>
                        
                        <p><b>Location</b>
                        <br />
                        ".$info['address']."</p>
                        <p>Telephone: ".$info['telephone']."
</p>            <p>Keywords: <i>".$info['keywords']."</i>
                    <hr /> ";};}
                        
                        
                        
        
  1. $keyword = $_GET['keyword'];
  2.             
  3.             
  4.             $sql = "SELECT * FROM listing WHERE MATCH(companyname,type,description,address,keywords)
  5. AGAINST('+$keyword* ~term' IN BOOLEAN MODE) ORDER BY keywords ASC " ;
  6.                     
  7.             $results = mysql_query($sql) or die("Query failed : " . mysql_error() . mysql_errno());
  8.     
  9.         }
  10.     
  11.      $tester = @mysql_num_rows($results);
  12. if ($tester == 0) {echo "<p>Your search returns no results, please try again!!</p>
  13. ";} else {
  14. echo'<p><strong>Your search for '.$keyword.' returned the results below:</strong><br />
  15. <span class="style1">If you contact any of the companies below please mention Around Dundee as the place you found them!!</span><hr /></p>';
  16.     $sql = "SELECT * FROM listing WHERE MATCH(companyname,type,address,shortdescription,description,keywords)
  17. AGAINST('+$keyword* ~term *$keyword+ ~term' IN BOOLEAN MODE) ORDER BY RAND()" ;
  18.                     
  19.             $results = mysql_query($sql) or die("Query failed : " . mysql_error() . mysql_errno());
  20.     
  21.         
  22.     
  23.                 while ($info = mysql_fetch_array($results))
  24.                 {
  25.                 
  26.                 echo"
  27.             
  28.                         <p class='thename'><b>Name: ".$info['companyname']."</b></p>
  29.                         
  30.                         <p><b>Location</b>
  31.                         <br />
  32.                         ".$info['address']."</p>
  33.                         <p>Telephone: ".$info['telephone']."
  34. </p>            <p>Keywords: <i>".$info['keywords']."</i>
  35.                     <hr /> ";};}
  36.                         
  37.                         
  38.                         
  39.         



Now for most stuff it returns the results i want .. but for a couple of things it returns nothing although i do see in the tables the words are there..

I think i know what people are going to type into my search box to find the things they want so in the keywords column i have inserted

"food take away"

Now if some one searches for take away food all the listings in the database associated with the term food take away or any thing to do with food, take, away will be returned to the viewer..

Well its not happening

You can have a look at the test site here


http://vps.we-w3c.org/~ad2cou/

I am trying to build a business search for my home city

any help would be great!!

quick edit

in the type field i am inserting some keywords .. the problem i am having is with take away food .. i am inserting take away food in this column so why isnt it returning the listings with it?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

well match against doesn't just return every row with the term in it ... in the case where you'd want that I'd suggest rather using "like", but with match against each column (that is fulltext indexed) gets a score with certain keywords and the score for those keywords are obviously too low ... see what happens when you repeat those words a few times more in the passages ... it should show up correctly now ...

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 43 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.