Jump to content

Archived

This topic is now archived and is closed to further replies.

mojito

mysql matching against error

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.