Suzanne — Mon, 17 May 2004 15:15:26 +0000

I notice that dock and iPod are four letter words. I know the cut off for FULLTEXT is supposed to be four or more characters, but I'm wondering if you either have an issue in the order of your indexed fields and the match statement or if perhaps the four characters aren't being indexed...

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
http://dev.mysql.com/doc/mysql/en/Fulltext_Restrictions.html

But wait! Apparently you can change the minimums and the stop list!

http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html

Let's explore together, eh? ha!

andy206uk — Mon, 17 May 2004 08:11:40 +0000

Absolutly. I've had it printing out the scores though, and for some bizzare reason, if you search for "dock" the pocketdock is returned with a zero score, even though the word "dock" is mentioned several times in the description (i've even tried putting "dock dock dock dock dock" in the results to give better results.

If I get chance later today, I'll copy the script online and make it print the scores so you can see what I mean.

I'm stumped.

Suzanne — Sun, 16 May 2004 20:15:09 +0000

andy206uk wrote: You can see it in action on my site. ipodx.co.uk

Very nice looking, btw.

Untested, but this is the idea -- select everything and then return only the parts you need. If this doesn't work, then simply do a conditional statement in php -- if ($score != 0) { // go ahead and return results }. I assume you have some php returning the information anyway?

<?php
    $sql = \"
    
    SELECT *, MATCH (name,description,longdescription,metakeywords,colours) 
    
        AGAINST ('$keyword') 
    
    AS score 
    
        FROM `products` 
    
    WHERE (status = 'active') 

    AND (score !=0)
    
    ORDER BY `score` 
    
    DESC
    \"; 
?>

andy206uk — Sat, 15 May 2004 18:45:20 +0000

You can see it in action on my site. ipodx.co.uk

Use the search facility on the site and search for "dock". I would expect a product called the sendstation pocketdock to show in the results (the word dock is mentioned several times in the description).

The product does occur but the score is 0 so doesnt show up and I've put on a filter to stop results with 0 relevence showing (afterall, if it's not relevent, what's the point?)

the SQL is:

	$sql = "
	
	SELECT *, MATCH (name,description,longdescription,metakeywords,colours) 
	
		AGAINST ('$keyword') 
	
	AS score 
	
		FROM `products` 
	
	WHERE (status = 'active') 
	
	AND 
	
		(MATCH (name,description,longdescription,metakeywords,colours) 
	
			AGAINST ('$keyword') > '0')
			
	ORDER BY `score` 
	
	DESC
	";

I did try doing it like

	$sql = "
	
	SELECT *, MATCH (name,description,longdescription,metakeywords,colours) 
	
		AGAINST ('$keyword') 
	
	AS score 
	
		FROM `products` 
	
	WHERE (status = 'active') 
	
	AND 
	
		`score` > '0')
			
	ORDER BY `score` 
	
	DESC
	";

However, that threw up an error, so i used the longer code which seemed to work. This problem also occurs if you search for iPod (which should, in theory return EVERY product in the site, but instead gives a 0 result).

Any ideas?

ta!

Andy

Suzanne — Sat, 15 May 2004 17:06:41 +0000

whoa, that's some heavy returns... what are the search terms? i.e. can you sort out for us bystanders what sort of results you're expecting and what you're getting and why and such?

andy206uk — Sat, 15 May 2004 12:58:12 +0000

Damn... doesnt seem to work as I expected. Apparently if more than half the results contain the search term then mysql doesnt return any results. Do you know if theres anyway round this?

andy206uk — Sat, 15 May 2004 10:37:35 +0000

Thanks suzanne! That worked a charm. I had to work out how to add a fulltext index, but once I got that sorted it was plain sailing!

TOP MOD!

Suzanne — Thu, 13 May 2004 16:01:12 +0000

(For FULLTEXT only)

<?php
$sql = \"SELECT 
            *, MATCH (name, description, longdescription,metakeywords) 
        AGAINST 
            ('$keyword') 
        AS 
            score 
        FROM 
            `products` 
        AND 
            (status = 'active') 
        ORDER BY score DESC\";
?>

Then you can display the score for any particular match.