a bit stumped on an algorithm

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

a bit stumped on an algorithm

Post 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 :P

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 8)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

Post by Deemo »

im getting a error with my SQL query, here is how my database is set up:
Image

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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
Post Reply