match against but not whole words

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

match against but not whole words

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you tried "with query expansion?" That might help, but I'm not entirely sure.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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)
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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
Post Reply