using FULLTEXT searching - giving more weight to one column

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
StuManUSA
Forum Newbie
Posts: 2
Joined: Mon Sep 18, 2006 2:45 pm

using FULLTEXT searching - giving more weight to one column

Post by StuManUSA »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi Gang,

New here thanks for any help.

I'm using FULLTEXT searching on four columns within one table.  However, one of these columns I want to have more importance than the others: "Title".

I came up with a way to do this (kind of) by doing two MATCH ... AGAINST statements but it's not working exactly how I want.  Basically want I'd *like* is if someone searches for exactly the title of a particular Item, it will return that one first.  Not sure if this possible but it's something to try for.

Is there a more effecient way to give "Title" more weight as a column?

[syntax="sql"]
SELECT ItemID, ItemTypeID, CategoryID, Title, Tags, ShortDesc, CreatedDate, date, creationDate, archive, Region, DATE_FORMAT(date, '%M %d, %Y') AS PostedDate
	FROM tblItems
	WHERE MATCH (Title, Tags, ShortDesc, LongDesc) AGAINST ($search) AND MATCH (Title) AGAINST ($search) 

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I believe an ORDER BY using the MATCH statements would work to your satisfaction.

It should be noted that MATCH returns a number.. hint hint.
StuManUSA
Forum Newbie
Posts: 2
Joined: Mon Sep 18, 2006 2:45 pm

Post by StuManUSA »

Thanks this seems to return more what I had in mind. Decent, you think?

Code: Select all

SELECT ItemID, ItemTypeID, CategoryID, Title, Tags, ShortDesc, CreatedDate, date, creationDate, archive, Region, DATE_FORMAT(date, '%M %d, %Y') AS PostedDate, MATCH (Title) AGAINST ($e_qt) AS Score
	FROM tblItems
	WHERE MATCH (Title, Tags, ShortDesc, LongDesc) AGAINST ($e_qt)
	ORDER BY Score DESC
Post Reply