Questions about executing a word search query.

[onedumbcoder]

is there a shorter way of doing this:

 

(title LIKE CONCAT('%', '$searchword', '%') OR description LIKE CONCAT('%','$searchword', '%'))

 

also when a user enters a search string, do i want to explode that string or not?

 

if yes

 

do i explode it by white spaces?

 

if yes

 

what if they enter 9 words, wouldnt that mean I have to do 9 like statments for the title and 9 like statements for the description?

 

Also

 

how would I make it so that the results with the most word matches in it

show up first?

 

for example lets say we search "Learning Mysql"

 

3 results come back,

 

first one only has matched learning

second one only has matched mysql

third one has matched both mysql and learning

 

how would I make it so that the third match comes up first then the rest, the only solution I thought of was a union where I do an AND in the first query and an OR in the second, but when we are talking about multiple words, we will have a very large number of possible combination. so i must be missing something

 

 

 

 

[fenway]

Well, you're "faking" a proper fulltext index, so there isn't much a choice.  But you can roll-your-own rankings and weights.

[onedumbcoder]

so whats the proper way of doing it then?

[fenway]

You can look at FULLTEXT indexing, but that has all sorts of limitations.