searching mysql with php

[sir pannels]

hi all,

O.K after maybe abit of a brainstorm here regarding searching mysql.

I have a field... decription.... in that varchar field is around 10-100 characters describing that row. I wrote a search that just took a search term.. for example... blue shoes ... and puts that directly into a query..

SELECT this FROM that WHERE description LIKE '%$search_term%'

That is all well and good .. however some of the data is untidy ... in some cases you have rows with a decsription of either blue shoes or even shoes blue .. so the LIKE %% does not find all blue shoes description.. so I then used explode to get each word from the search term and then did a foreach word a sperate LIKE %% in the query... the problem I know have is that I now return rows for blue shoes,shoes blue, blue, shoes.. idealy i wouldnt have results for just blue and shoes.. having those results would not matter too much if I was able to weight the results some how.. order them so that the results with the most matches are at the top etc.

Anyone have any thoughts on what I'm trying to pull off here?

cheers all,
Sir P :D

[kairog]

hi friend,

Maybe you may take advantage of the "FULLTEXT" index. Add a fulltext to the 'description' field and create sql statement like this.

SELECT
*, MATCH (description)
AGAINST
('search_term')
AS
score
FROM
table_name
ORDER BY score DESC

Hope that helps

Cheers,

[sir pannels]

hi kairog,

thanks for your helpful reply... I set fulltext index on description and ran your query and it does indeed order it correctly.. that much is perfect thanks :) However it actually returns every single row in the DB.. some that don't have anything similar to the search term at all... did I miss something?

cheers again :)

[kairog]

Hi Sir pannels,

Here's a good place for you to start
http://www.hudzilla.org/phpbook/read.php/9_3_18

Hope that helps

[abduraooft]

http://devzone.zend.com/node/view/id/1304 also may help you!

[brazenskies]

SELECT
*, MATCH (description)
AGAINST
('search_term' IN BOOLEAN MODE)
AS
score
FROM
table_name
ORDER BY score DESC

[sir pannels]

thanks for all the replies and the links.. I'm just starting to go through all that information now :)

Thanks for the query, brazenskies... unfortunetly it still returns all rows.. it looks to actually do the same thing as the first query in the post. Many thanks for your time anyway