Searching with MATCH AGAINST

crawfd

I have the following query string:

index.php?searchwords=searchword&category=7

I'm attempting to get the values from the string and use it in my search query by by category.

When I type in a keyword to search, i.e 'searchword', it doesn't return anything even though the Name of the product is 'searchword' and is in the category I've selected. Any thoughts?


elseif(isset($_GET['searchwords'])){

$query2 = "SELECT name from categories where id='$_GET[category]'";
$res=mysql_query($query2) or die("SQL: $query<br />\nError: ".mysql_error()); 
$row=mysql_fetch_array($res);

echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"10\"><tr><td class=\"title1\">";
echo "Category: <b>$row[name]</b>";
echo "</td></tr></table>";

$queryn = "SELECT * from products where category = '$_GET[category]' AND MATCH (name) AGAINST ('$_GET[searchwords]')";
$res=mysql_query($queryn) or die("SQL: $query<br />\nError: ".mysql_error()); 
$num_rows = mysql_num_rows($res);

if (($num_rows) == 0) {

echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"10\"><tr class=\"cells\"><td class=\"body1\" colspan=\"6\"><font color=red>No templates found. Please select another search criteria.</font></td></tr></table>";

} else {


while ($rowp=mysql_fetch_array($queryn)){
echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"10\"><tr><td class=\"body1\" width=\"1%\"><a href='index.php?product=$rowp[id]'><img src='images/thumbs/$rowp[photo]' border=0></a></td><td class=\"body1\" valign=\"top\"><p class=\"cartheader\">Template Name: <b>$rowp[name]</b></p>
      <p>$rowp[description]</p>
      <p><strong>Price: <b><font color=red>$rowp[price]</font></b> | <a href='catalog.php?product=$rowp[id]'>Add to Cart</a> | <a href='images/$rowp[photo]' target='_blank'>Enlarge Image</a></strong></p></td>
  </tr>
</table>
<hr size=\"1\">";

}
}


}

Roger_Ramjet

Well your query looks ok, assuming that the full text index exists for the match operation. Echo out the query strings before you execute them to see what is actually being passed to mysql, probably be a simple error like missing quotes.

EDIT: Except , I always write mine thus
$GET['searchwords'] not $GET[searchwords].

crawfd

I echoed the query result and have the following:

SELECT * FROM products WHERE category = '7' AND MATCH (name) AGAINST ('the man hat')

the man hat are the keywords I entered in the previous form.

I believe I have the full text definition properly added to the 'name' field which seems to be necessary for a 'MATCH AGAINST' search criteria. I printed out my table structure. Is there a problem with this? Added a full text to the 'name' field which is the field I'm using in the search criteria for my query.

CREATE TABLE products (
id int(11) NOT NULL auto_increment,
name text NOT NULL,
description text NOT NULL,
price decimal(10,2) NOT NULL default '0.00',
photo varchar(50) NOT NULL default '',
category int(11) NOT NULL default '0',
quantity int(11) NOT NULL default '0',
PRIMARY KEY (id),
FULLTEXT KEY name (name)
) TYPE=MyISAM;

Last edited by crawfd

Roger_Ramjet

OK, well full text search does not match little words:

"Some words are ignored in full-text searches:

* Any word that is too short is ignored. The default minimum length of words that will be found by full-text searches is four characters.
* Words in the stopword list are ignored. A stopword is a word such as ``the'' or ``some'' that is so common that it is considered to have zero semantic value. There is a built-in stopword list.

The default minimum word length and stopword list can be changed as described in section 12.6.4 Fine-Tuning MySQL Full-Text Search. "

full text search

Try it with some 5-6 letter words.