[web] ASP+Access: Search page, ordered by relevance
Author
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.
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?
Author
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.
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
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
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!
Author
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
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
---
Michael Tanczos
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:
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]
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 tblEntryWHERE [text] LIKE '%A%' AND [text] LIKE '%B%'UNION ALLSELECT [name], 1 AS [relevance] FROM tblEntryWHERE ([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]
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.
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
then join this temporary table, ordering by the score you've accumulated
Mark
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
