Search Database: Results for any word in query

rsmith

Hey all,
below is my current search script that goes and finds items in an inventory database. The script works great for individual words or string of words as long as the string is exact. I.E. search "handgun, black" will return 1 result because the description contains that EXACT phrase. However, if I search "handgun black" without the comma, I get 0 results. How can I make the search return results regardless of punctuation, in other words, find all words entered into the search query?

<?
	// Get the search terms
		$term = trim($_GET['term']);

	$query = "SELECT id, item, descr, retail_price, qty FROM inventory WHERE item LIKE \"%$term%\" || descr LIKE \"%$term%\" ORDER BY id";
	$result = mysql_query($query);
?>
  <form method="get" action="/tucsonguns/inventory/product-search.php" name="prodsearch">
    <table align="center" border="0" cellpadding="2" cellspacing="0">
      <tr>
        <td class="large" valign="middle" colspan="2">
          <i><b>PRODUCT SEARCH</b></i>
        </td>
      </tr>
      <tr>
        <td valign="middle" height="10">
        </td>
      </tr>
      <tr>
        <td valign="middle" width="600">
          Enter your search term in the box on the right. You will be shown a list of results below
        </td>
        <td valign="middle" width="200">
          <input type="text" class="textbox" name="term" size="15"> <input type="submit" class="button" value="Search!">
        </td>
      </tr>
    </table>
  </form>

<?
  if(isset($term)){
	  if($term != ""){

  	echo "<table align=center border=0 cellpadding=0 cellspacing=0 width=800>";
    echo "<tr>";
    echo "<td valign=middle>";
    echo "<b>Your Search for <font color=#ff0000>".$term."</font> returned ".mysql_num_rows($result)." results.</b>";
    echo "</td>";
    echo "</tr>";
    echo "<tr>";
    echo "<td valign=middle height=15>";
    echo "</td>";
    echo "</tr>";

    	while($items = mysql_fetch_assoc($result)){
	    	echo "<tr>";
	    	echo "<td valign=middle>";
	    	echo "<b class=tdheader>".$items['item']."</b><br />";
	    	echo $items['descr']."<br />";
	    	echo $items['qty']." in stock.<br /><br />";
	    	echo "</td></tr>";
    	}
	}
}

echo "</table>";
?>

Any help is greatly appreciated!

bogu

Try using a FULL-TEXT SEARCH ...

liquorvicar

As bogu says, you could use a FULLTEXT index/search. However this is a native MySQL function and isn't portable. For full portability you might want to build your own search engine function. There's a good article about doing the later in the Sept issue of PHP Architect

rsmith

Thanks all. I did some researching on the FULLTEXT searches and implemented it best I could. Here's my new query:

$query = "SELECT * FROM inventory WHERE MATCH(item, descr) AGAINST('".$term."') ORDER BY id";

The problem now is that it returns no results. I'm probably screwing up the syntax in the AGAINST() statement but I'm not getting any error codes or parse problems. Just no results. Any ideas?

Thanks!

liquorvicar

There are various issues with MySQLs FULL-TEXT searches, one being that if your search string matches more than 50% of the rows in a table nothing is returned. Try a less common search term and see if that works.

rsmith

Okay I have it working somewhat. The problem now is that it's only searching the last word entered in the search string, not all the words individually.

liquorvicar

Are you definitely not getting any errors?
Did you create your FULL-TEXT index?
Have you made sure you're not searching for any stop-words (MySQL ignores a number of common words in FULL-TEXT searches).

rsmith

Yeah I created the FULLTEXT index according to the MySQL tutorial I found above. Here's the entire code, maybe it will give new perspective (minus the includes pertaining to my db connection info:

<?
	// Get the search terms
		$sterm = $_GET['term'];
		$terms = explode(" ", $sterm);

	foreach($terms as $term){
		#$where = "item MATCH(item, descr) AGAINST($term);"
		$where = "item LIKE '%$term%' || descr LIKE '%$term%'";
		$query = "SELECT id, item, descr, retail_price, qty FROM inventory WHERE $where";
		$result = mysql_query($query);
	}		
?>
  <form method="get" action="/tucsonguns/inventory/product-search.php" name="prodsearch">
    <table align="center" border="0" cellpadding="2" cellspacing="0">
      <tr>
        <td class="large" valign="middle" colspan="2">
          <i><b>PRODUCT SEARCH</b></i>
        </td>
      </tr>
      <tr>
        <td valign="middle" height="10">
        </td>
      </tr>
      <tr>
        <td valign="middle" width="600">
          Enter your search term in the box on the right. You will be shown a list of results below
        </td>
        <td valign="middle" width="200">
          <input type="text" class="textbox" name="term" size="15"> <input type="submit" class="button" value="Search!">
        </td>
      </tr>
    </table>
  </form>

<?
  if(isset($term)){
	  if($term != ""){

  	echo "<table align=center border=0 cellpadding=0 cellspacing=0 width=800>";
    echo "<tr>";
    echo "<td valign=middle>";
    echo "<b>Your Search for <font color=#ff0000>".$term."</font> returned ".mysql_num_rows($result)." results.</b>";
    echo "</td>";
    echo "</tr>";
    echo "<tr>";
    echo "<td valign=middle height=15>";
    echo "</td>";
    echo "</tr>";

    	while($items = mysql_fetch_assoc($result)){
	    	echo "<tr>";
	    	echo "<td valign=middle>";
	    	echo "<b class=tdheader>".$items['item']."</b><br />";
	    	echo $items['descr']."<br />";
	    	echo $items['qty']." in stock.<br /><br />";
	    	echo "</td></tr>";
    	}
	}
}

echo "</table>";
?>

If I do the search I can see that all the search terms were passed into the URL but the script only takes the last one. An example search would be: black handgun" I get results but only for the word handgun when both words are present in some items.

liquorvicar

THis'll be your problem:

foreach($terms as $term){
            #$where = "item MATCH(item, descr) AGAINST($term);"
            $where = "item LIKE '%$term%' || descr LIKE '%$term%'";
            $query = "SELECT id, item, descr, retail_price, qty FROM inventory WHERE $where";
            $result = mysql_query($query);
        }

Basically, you'll only ever get the last query's results as you're overwriting $result each time.
You probably need something like this:

$where = array();
foreach($terms as $term){
    $where[] = "item LIKE '%$term%'";
    $where[] = "descr LIKE '%$term%'";
}
$query = "SELECT id, item, descr, retail_price, qty FROM inventory WHERE ".implode(' or ',$where);
$result = mysql_query($query);

Of course you need to make sure that $terms and therefore $where aren't empty arrays or your query will fail.

rsmith

Thanks for all the help. I tried doing it as described in the post above by building a where[] array but I get the same result.

SpinCode

Hi,

Judging from your problem, I believe the best way to do it is to use MySearchParser.

http://www.addedworth.com/php/urtfer.php?t=MySearchParser

I purchased it about 2 weeks back, and it's working wonders.

Hope this helps,
Spin

liquorvicar

rsmith - did you solve this? It may be something to do with the way you are retrieving the result set. Have you tried echoing the query out and running it directly on the database (via PHPMyAdmin or similar)?

Sxooter

liquorvicar wrote:
As bogu says, you could use a FULLTEXT index/search. However this is a native MySQL function and isn't portable. For full portability you might want to build your own search engine function. There's a good article about doing the later in the Sept issue of PHP Architect

Note, however, that virtually every database made has some kind of full text indexing feature. MSSQL, Oracle, PostgreSQL, DB2, all have full text search engines. Generally they're more work up front, and often the indexes aren't updated in real time, but they're much much faster at looking up various text entries in the database than the other way.

OTOH, if your tables aren't real big, it's quite easy to build a search query that hits x fields with y words.

For instance:

<?php
$query = "select * from table where (";
$words = array("hello","again");
$fields = array("field1","field2","field3");
$l=0;
foreach ($words as $w){
    if ($l!=0) $query.=") AND (";
    $l=1;
    $m=0;
    foreach($fields as $f){
        if ($m!=0) $query.=" OR ";
        $query.=" $f like '%$w%' ";
        $m=1;
    }
}
$query.=")";
print $query;
?>

Just change your array entries and you can search for more words on more fields etc... I leave it to you to parse a search string into individual words and put them in an array. (hint, explode is good for that)