Page 1 of 1

match against but not whole words

Posted: Mon May 01, 2006 9:26 am
by []InTeR[]
I have a problem with match against.

I wanna use match against for sorting relevance, and searching the records. But i need the some reccords to be found as well, thats not found atm.

When a user searches for 'car', it needs to find 'carwheels' and 'carphone' as well. And 'car' with higher relevance.

Din't check the query, but it's something like this.

Code: Select all

SELECT
`items`.*,
MATCH (`title`,`description`) AGAINST ('car') AS `rel`
FROM `items`
WHERE
MATCH (`title`,`description`) AGAINST ('car')
ORDER BY `rel` DESC
If the title or description doesn't have exact 'car' in them as a word i wont be found.
And AGAINST ('car%') doesn't seem to work.

Posted: Mon May 01, 2006 5:17 pm
by feyd
Have you tried "with query expansion?" That might help, but I'm not entirely sure.

Posted: Mon May 01, 2006 5:44 pm
by raghavan20
This might be helpful.

The word for which you want higher relevance match, have to be in the search string and also search string *. You can understand more from the example I have provided below.

Code: Select all

mysql>  show create table forSearch
    -> ;
+-----------+------------------------------------------------------------------------------
----------------------------+
| Table     | Create Table
                            |
+-----------+------------------------------------------------------------------------------
----------------------------+
| forSearch | CREATE TABLE `forsearch` (
  `someText` varchar(50) default NULL,
  FULLTEXT KEY `someText` (`someText`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql> select * from forSearch;
+----------+
| someText |
+----------+
| car      |
| car2     |
| carsales |
| carwash  |
| caravan  |
+----------+
5 rows in set (0.00 sec)

mysql> select *, match(someText) against ('car car*' in boolean mode) from forSearch
    -> where match(someText) against ('car car*' in boolean mode);
+----------+------------------------------------------------------+
| someText | match(someText) against ('car car*' in boolean mode) |
+----------+------------------------------------------------------+
| car      | 2                                                    |
| car2     | 1                                                    |
| caravan  | 1                                                    |
| carsales | 1                                                    |
| carwash  | 1                                                    |
+----------+------------------------------------------------------+
5 rows in set (0.00 sec)

Posted: Tue May 02, 2006 1:44 am
by []InTeR[]
feyd wrote:Have you tried "with query expansion?" That might help, but I'm not entirely sure.
query expansion is for 'related' results.
It takes the first resultset it gets back from the 'normal' rearch, and do the search against with the words that are in that record.
(globaly)

Posted: Tue May 02, 2006 1:45 am
by []InTeR[]
raghavan20 wrote:This might be helpful.

The word for which you want higher relevance match, have to be in the search string and also search string *. You can understand more from the example I have provided below.
I think this will help, will try it.

Posted: Tue May 02, 2006 3:17 am
by []InTeR[]
Yep, it worked.

I used

Code: Select all

$wildSearch = preg_replace('/ +/', '* ', $searchstring).'*';
To make 'car house' to 'car* house*'.

And then the sql code

Code: Select all

SELECT *,
(MATCH (`searchfield`) AGAINST ('".$searchstring."' in boolean mode)*2 +
MATCH (`searchfield`) AGAINST ('".$wildSearch."' in boolean mode)) AS `rel`
FROM `items`
WHERE 
MATCH (`searchfield`) AGAINST ('".$searchstring." ".$wildSearch."' in boolean mode)
ORDER BY rel` DESC
So that i have the exact words first, and then the wildcard search.

Hope's it help somebody out