MySQL Full Text Search: Select rows with score higher than some percentage?

Databases
1

Greetings,

I have a Full Search feature on my site now. I am not getting the results I wanted. Either the results become too restrictive or not relevant enough. I had a score at one time, but I cannot order by score because I am ordering by other important fields (new listings, highest prices, lowest prices, etc…). I’ve also been trying IN BOOLEAN MODE with various methods as well with no luck.

So I was wondering if there was a way to select the rows that have a score higher than some set value (0.75 in this case), like this:


$sql = "SELECT *,MATCH (title,description) AGAINST ('$searchphrase' IN BOOLEAN MODE) as score FROM items WHERE MATCH (title,description) AGAINST ('$searchphrase' IN BOOLEAN MODE) AND score > 0.75 ORDER BY (sellprice + shipprice) ASC";

Let me know if this is possible. Also, what are some good algorithms that you use?

2

Thanks for the help. I added a SELECT before MATCH this time and the error goes away.

There is still a problem, this query is bringing up no results. For example, I am trying to do a simple count to see how many rows this calls up:

SELECT COUNT(*) as num
  FROM table 
 WHERE (SELECT MATCH (title,description) AGAINST ('$searchphrase') as score) > 
       '$math'

This is bringing up a count of 0 for any value.

3

Greetings,

I’m looking for a way to retrieve a count value and apply it within the same query such as:

$sql = "(SELECT COUNT(*) as num FROM table WHERE product = 'some_value') SELECT * FROM table WHERE field > 'num'";

Basically I am trying to first get a count as “num” and then use that same “num” value as a condition in the next part of the query. This needs to be done all within one query. How can this be done?

Thanks

4 
SELECT * 
  FROM table 
 WHERE field > 
       ( SELECT COUNT(*) as num        
           FROM table                  
          WHERE product = 'some_value' )
5

How come this won’t work?:

SELECT * 
  FROM table 
 WHERE (MATCH (title,description) AGAINST ('$searchphrase') as score) > 
       '$math'

Thanks for responding, I’m trying to come up with an accurate search feature, such as selecting only results that contain at least 50% of words inside a search phrase.

6

can you please describe what happens?

does it run? does it crash? does it give an error message?

7

It basically gives a Warning error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

8

could you run the query outside of php please? substitute some actual values for your php variables

9

I entered this into phpMyAdmin manually with actual values and got the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘as score) > ‘2’ LIMIT 0, 30’ at line 1

10

excellent :slight_smile:

the mysql error message tells you exactly where the problem is

it turns out that you can assign a column alias (i.e. expression AS alias) only in the SELECT clause

you’re trying to do that in the WHERE clause and you cannot

11

sigh

not sure why you want to count rows, but your query should look like this –

SELECT COUNT(*) as num
  FROM table 
 WHERE MATCH (title,description) AGAINST ('$searchphrase')
        > '$math'

see? no alias in the WHERE clause

not sure if the relevance score returned by MATCH is going to satisfy your needs the way you think

why don’t you do this and see the relevance score for all of your rows –

SELECT title
     , description
     , MATCH (title,description) AGAINST ('$searchphrase') AS score
  FROM table 
ORDER
    BY score DESC

see? alias in the SELECT clause

12

Awesome, thanks a lot! This seems to be working good so far. I guess I didn’t need to use “as score” to create a number to compare with “$math”.

I can’t use the bottom one because it orders by score. What I needed to do was get the words 50% to 75% accurate (collect the cream of the crop) and display it. I have other sort fields like eBay where you can display (ORDER BY) prices, dates, ending soonest and so on. But once I cut off the cream of the crop listings, ordering by score doesn’t matter. I also needed to count the rows to display the number of items up for auction that are found in the sort as well.

I will need to play around with this tomorrow morning to see if it’s working like I think.

Thanks
Kind regards

13

the only reason i suggested the 2nd query, to dump all your rows with the score for each row, was so that ~you~ could see the score values

no sense putting a WHERE clause filter to require score being greater than 50 if the score values are between 0 and 1, eh?