mysql matching against error

[mojito]

I cant seem to see where is the syntax error in the following

[code]"SELECT crit_string,ID FROM clients MATCH(crit_string) AGAINST ('ee')"[/code]

please anyone?

i dont have to specify the db as thats in the connection and i specify the table as clients, im looking to search in the field crit_string for 'ee' i have set the full text for that field also.

thanks

[mojito]

All I need is to check a string separated by commas for particular values, rather than have to get each field split it and test it for the value.

a string could be (in the db) "sandy_bay,2,4,55" for example and i might want to test for 2. and get a positive result in this case.

thanks guys.

[wickning1]

I'm not sure that MATCH AGAINST will work with commas, but it should.

But in order for it to work, you must put a FULLTEXT index on the column you wish to search. If you don't, MySQL returns a syntax error.

Note that it will drop certain trivial words from the index unless you tell it not to, and may not index numbers, I'm not sure. In those cases the result would (incorrectly) come back empty.

You can use a REGEXP search to make sure it doesn't do anything tricky, but it will be slow.

[fenway]

Not to mention that it ignores words of less than a given length (usually 4), and likes "words" -- hence the name FULLTEXT index. I won't even venture to ask why such non-atomic data is being stored in a DB field; but you might as well use a SUBSTR() call and find it that way. Search for ",<your str>," and match against your field with commas on either side to prvent partial matches.