Page 1 of 1
a bit stumped on an algorithm
Posted: Mon May 16, 2005 4:31 pm
by Deemo
Basically, i have a database full of articles. i need to be able to search through the entire database and pull up a list of all relevant articles (similar to how the forums search function works). I dont want to have to ask the user for specific keywords, because as well all know, the user is stupid
So what im asking now is, how would i go through creating this search? i dont need specific code necessarily, just some ideas or maybe a link to an algorithm or something
Thanks
Deemo

Posted: Mon May 16, 2005 5:35 pm
by pickle
If you don't get keywords from a user, what criteria are you going to use to base your search on.
You want relevant articles, but relative to what?
Posted: Mon May 16, 2005 5:43 pm
by Burrito
Pickle is right in that you have to search for SOMETHING...
I would use a full text indexed field on the MySQL database and do a MATCH AGAINST query to see if there are any relevant matches. With full text indexed fields it doesn't have to match exactly as it's entered for the search criteria...
for example:
searching for: "my dog has big teeth"
could return: "this is an article about dogs, dogs have teeth that are big"
lemme know if you need some help setting up the index or if you need query syntax help.
Burr
Posted: Mon May 16, 2005 5:47 pm
by pickle
Ya, FULLTEXT is probably the way to go. However, depending on the context, it may not be.
FULLTEXT does not work if you want to use wildcards, you'll have to use LIKE syntax for that.
For example, if I want to search the database for 'Madagascar', but I don't know how to spell it, I could type in "Mad%car" in the search field and search for that string using LIKE. Using FULLTEXT, however, it will just return entries that have the literal string 'Mad%car' in it.
Posted: Mon May 16, 2005 9:15 pm
by Deemo
i think fulltext is probably what i want. the way the search is going to work is exacly how the forum search works.
http://dev.mysql.com/doc/mysql/en/fulltext-search.html
just clarifying, is that what you are talking about?
Posted: Mon May 16, 2005 9:42 pm
by Burrito
yup that's what you want....but that is NOT exactly how the phpBB forum search works...it's more like a google approach to searching whereas the forums use keywords and separate them by spaces.
if you search for "dog biscuits, rabbit gravy flavored", in the forum search it's going to do a literal search for those words (anywhere in the "article") but the full text search doesn't care if all of the words match, nor the placement of the words. It uses a pretty complex algorithm to determine if articles match based on the search string (placement of words in the search string and placement of words in the searched text) then fetches results based on weight.
you can actually display the results by most relevant at the top, and then move downward as the results stray further from the search text. (just like a search engine).
as I posted before, if you need any help with the sql syntax, lemme know. Having them ordered by the most relevant is not super intuitive and not well documented (at least it wasn't when I started doing it).
Burr
Posted: Tue May 17, 2005 4:26 pm
by Deemo
im getting a error with my SQL query, here is how my database is set up:
the query im using is:
Code: Select all
SELECT * FROM News WHERE MATCH(Title, Text) AGAINST('blah')
and the error i get is
Code: Select all
Can't find FULLTEXT index matching the column list
what does this mean?
Posted: Tue May 17, 2005 4:35 pm
by Burrito
I can't tell from that if they're set up as full text indexed fields or not:
try running a query like this:
Code: Select all
create fulltext index %indexname% on %tablename% (%fieldname1%,%fieldname2%)
you have to give the index a name because it rebuilds the table data and puts it into a new "table" for storage of all of the values.