Page 1 of 1

using FULLTEXT searching - giving more weight to one column

Posted: Mon Sep 18, 2006 2:57 pm
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]

Posted: Mon Sep 18, 2006 3:11 pm
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.

Posted: Mon Sep 18, 2006 3:32 pm
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