It is easy to get around this limitation: for each column containing indexable content, create a shadow column containing content prepared for FULLTEXT indexing. I usually do the following: - for each word: - replace all non whitespace, non-blank characters by "_" - right pad to 4 with "_" for example "B.M.W." become "B_M_W_", "tax" will become "tax_"
now you prepare similarly the string to MATCH actually doing so is good personal hygiene regardless of the value of ft_max_len set by the host. it is mandatory when you deal with languages which do not separate words by whitespaces (in my case Japanese) HTH Ignatius _________________________ ----- Original Message ----- From: "Gavin Amm" <[EMAIL PROTECTED]> To: "Ignatius Reilly" <[EMAIL PROTECTED]> Sent: Wednesday, April 28, 2004 10:03 AM Subject: RE: [PHP-DB] MySQL - counting number of instances of a word in a field Yes, They do. Unfortunately the site is hosted by an ISP & has set the indexed words to >3, the site requires essential words of 3 char length to be searched through, such as "tax", "ato", "cgt", etc... I will certainly still index the 2 main columns in the table... Thanks for your help. -----Original Message----- From: Ignatius Reilly [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 April 2004 4:34 PM To: Gavin Amm; [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL - counting number of instances of a word in a field The functions do not exist. However, FULLTEXT indexes will do much of this work for you. I encourage you to experiment with them. _________________________ ----- Original Message ----- From: "Gavin Amm" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 28, 2004 7:18 AM Subject: [PHP-DB] MySQL - counting number of instances of a word in a field Hi guys, I'm trying to find a MySQL function(s) that will allow me to count the number of words in a field. For eg: [table: pages] id - title - keywords - body 1 - Home - home, page - This is my home page. <p> Enjoy your stay. 2 - Feedback - feedback, form, contact - Please enter any feedback or comments in the form below.<br>Your feedback will be used to improve our service. How do I, for example, count the number of instances of a word such as "feedback" in say the BODY field? I'm putting together a search engine & I'd like to 'rank' the results based on the number of instances of each word found in a few fields. In this example, if they search for the words "feedback" and "improve", the count would result in the row id, word searched and the number of instances of that word: (formatting doesn't matter, it's just to give you an idea) id 2: "feedback" count = 2 id 2: "improve" count = 1 I can play with the weightings later, just need to figure out the counting... Thanks guys, Gav -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php