Jump to content


Photo

mysql matching against error


  • Please log in to reply
3 replies to this topic

#1 mojito

mojito
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 28 March 2006 - 10:18 PM

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

"SELECT crit_string,ID FROM clients MATCH(crit_string) AGAINST ('ee')"

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


#2 mojito

mojito
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 28 March 2006 - 10:38 PM

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.


#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 28 March 2006 - 10:56 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 March 2006 - 09:33 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users