Jump to content
  • Advertisement
Sign in to follow this  
boolean

[web] ASP+Access: Search page, ordered by relevance

This topic is 4724 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a search form on a page that, when the results are returned, I want to have it ordered by the relevance of the entries (So those with the most keyword hits come up first). I can't for the life of me figure out how I would do this. Anyone ever used one of these? I tried googling, but all the info assumes I am using an Index Server.

Share this post


Link to post
Share on other sites
Advertisement
So you have all of the text you want to search through in the Access database? Has it been proccesed at all, or is it just what you would read on the page?

Share this post


Link to post
Share on other sites
Well at the moment it is just searching on the same text that is returned to the page which the user would read. I probably should make a seperate keyword table.

Share this post


Link to post
Share on other sites
Normally I'd suggest using fulltext indexing.

This works with SQL server, although it's a bit annoying to set up and violates some of SQL server's normal rules (because it runs in an external service called "Microsoft Search").

But it works.

I dunno about MSAccess, but I strongly recommend not using it for the backend to an ASP page - it will result in poor performance and (in my experience) data corruption.

Mark

Share this post


Link to post
Share on other sites
I second the recommendation against using Access. I once had to build an ASP front for an Access database at work. It ran for about 3 weeks before promptly falling flat on its face! The only way I could get it back was creating a new database and importing all the data across. We switched it to MySQL after that, and its been running non-stop for a year and a half now!

Share this post


Link to post
Share on other sites
The reason for using access is that my sites are being hosted on the 'GDnet hosted sites' area, which (to my knowledge) only supports access

Share this post


Link to post
Share on other sites
Relevance is a tricky beast and increases the complexity of the project by a lot. With only microsoft access, I don't think it is reasonably possible to do.

---
Michael Tanczos

Share this post


Link to post
Share on other sites
The only thing I can think of is doing multiple queries.
I wouldn't recommend it, but I'm afraid it's the only way possible.
You could split up the keywords and first search for records containing them all, then searching for them containing only some or one of them.
To clarify my poor explanation, using some pseudo-SQL:
keywords: "A", "B"

SELECT [name], 0 AS [relevance] FROM tblEntry
WHERE [text] LIKE '%A%' AND [text] LIKE '%B%'

UNION ALL

SELECT [name], 1 AS [relevance] FROM tblEntry
WHERE ([text] LIKE '%A%' AND [text] NOT LIKE '%B%') OR ([text] LIKE '%B%' AND [text] NOT LIKE '%A%')

ORDER BY [relevance], [name]

I don't think I'll have to tell you that this may seem reasonable for one or two keywords, but dreadful when using three or more keywords (the number of unions increases in an exponential fashion).
So for the love of God, don't do it! [grin]

Share this post


Link to post
Share on other sites
There's no automagic way of doing this with Access that I know of. Aside from using multiple queries, if the recordsets are small enough you could order them yourself in ASP.

Share this post


Link to post
Share on other sites
There are a bunch of ways of doing it - you could create entries in a temporary table (or temporary entries in a permanent table) containing the items with keywords and add them up,
then join this temporary table, ordering by the score you've accumulated

Mark

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!