Doing a search on multiple mysql fields

[chomedey]

I am implementing a basic search feature, where the user can plug in a keyword, and if it is like any word in any of a number of fields, that row will be returned.

 

The query currently looks like this:

 

$query = "SELECT *, DATE_FORMAT(last_updated, '%e %M, %Y') as newdate

FROM profiles

WHERE username OR age OR gender OR education OR country OR state OR pseudonym OR things OR books OR songs OR movies OR people OR core_beliefs OR core_values OR live OR sad OR change_world

LIKE '%".$searchterm."%'

ORDER BY newdate

DESC LIMIT $start, $display";

 

But it's giving me some weird results.  Do I have to say something like

 

WHERE username LIKE  '%".$searchterm."%' OR age LIKE  '%".$searchterm."%' etc. etc.

 

That seems incredibly laborious.  Is there a quicker way?

 

Thanks.

 

Julian

[chomedey]

It works well enough with each field followed by the LIKE '%".$searchterm."%' condition, but if a row has a username of john, say, and a gender of Male, and you put 'john Male' into the search it returns no results, even though if you put just 'john' or just 'Male' you get a result.

 

Is there any way of making these conditions non-exclusive?

 

Thanks.

 

Julian

[andrewgauger]

Enable fulltext search on the columns and use match () ... against

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match

[chomedey]

Thanks for that.

 

I recreated the table and rewrote the query so it now reads:

 

$query = "SELECT *, DATE_FORMAT(last_updated, '%e %M, %Y') as newdate

FROM profiles

WHERE MATCH (username, gender, education, country, state, pseudonym, things, books, songs, movies, people, core_beliefs, core_values, live, sad, change_world)

AGAINST ('".$searchterm."')

ORDER BY newdate

DESC LIMIT $start, $display";

 

But now I'm getting no results (I have recreated profiles, obviously, to test against).

 

Any ideas?

 

Thanks.

 

Julian

[chomedey]

I think this may be the problem:

 

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows.

 

J

[chomedey]

Yes that was the problem.

 

Thanks very much for your help.

 

Best,

 

Julian

[chomedey]

Sorry - one other question ...

 

Is it possible to make it so it will pull out a row with 'apples' in it if you search on 'apple'?  Right now it needs an exact search, and if I put in '%$searchterm%' it doesn't seem to make any difference.

 

Cheers.

 

Julian

[andrewgauger]

match("apple*" IN BOOLEAN) but it is more trouble than it is worth