Jump to content

mysql matching against error


mojito

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.