Searching

[Guest]

I have just installed coldfusion 9 and starting development on a project, but the client wants a way to be able to perform search engine type queries on a database.

Does anyone have any experience with this that could maybe shed some light. Any help is very much appreciated.

Thank you.

[Dan_Bracuk]

Do you know how to write sql?

[Guest]

Yes, but how would i be able to write a search query in SQL. For example, lets say i had the following data in my db

ID                                                             DATA

1                                                        The quick brown fox jumps

2                                                        There is a fox over there

3                                                        That girl is foxxy

How would i be able to write sql to be able to search that data and give me everything that has the word fox in it and make it display by relevance, meaning that it would obviously display the first 2, but it should also display the last one but would make it last because its not exactly "fox" but it includes the word in there.

[Guest]

you can use wild cards,

select ID rom mytable where DATA like '%fox%'  [for mysql ]

but that is pretty lightweight and won't help you if you need to weigh the searches,... [i.e. fox appears more than one time in any record]

just google around for "full text search" I hit 3 or 4 useful looking articles in the first result.

-sean