Extract specific words from text field in mysql

问题:

I have a table that contains a text field, there is around 3 to 4 sentences in the field depending on the row.

Now, I am making an auto-complete html object, and I would like to start typing the beginning of a word and that the database return words that start with those letters from the database text field.

Example of a text field: I like fishsticks, fishhat are great too

in my auto-complete if I would type "fish" it would propose "fishsticks" and "fishhat"

Everything works but the query.

I can easily find the rows that contains a specific word but I can't extract only the word, not the full text.

select data_txt from mytable match(data_txt) against('fish', IN BOOLEAN MODE) limit 10

I know it is dirty, but I cannot rearrange the database.

Thank you for your help!

EDIT:

Here's what I got, thanks to Brent Worden, it is not clean but it works:

SELECT DISTINCT
SUBSTRING(data_txt, 
LOCATE('great', data_txt),
LOCATE(' ' , data_txt, LOCATE('great', data_txt)) - LOCATE('great', data_txt)
)
 FROM mytable WHERE data_txt LIKE '% great%'
LIMIT 10

any idea on how to avoid using the same LOCATE expression over and over?

回答1:

Use LOCATE to find the occurrence of the word.

Use LOCATE and the previous LOCATE return value to find the occurrence of the first space after the word.

USE SUBSTR and the previous 2 LOCATE return values to extract the whole word.

HTH.

回答2:

$tagsql ="SELECT * from mytable";
$tagquery = mysql_query($tagsql);

$tags = array(); //Creates an empty array

while ($tagrow = mysql_fetch_array($tagquery)) {

   $tags[] = tagrow['tags']; //Fills the empty array

}

If the rows contain commas you could use -

 $comma_separated = implode(",", $tags);

you can replace the comma for spaces if they are separated as spaces in your table.

$exp = explode(",", $comma_separated);

If you require your data to be unique you may include the following:

$uniquetags = array_unique($exp, SORT_REGULAR);

you can use print_r to see the results of the array resulting

Here array_merge is used because $rt will not get displayed if you are using a 'jquery' autocomplete else $rt may work and array_merge can be ignored. However, you may use array_merge to include multiple tables by repeating the previous process.

$autocompletetags = array_merge((array)$uniquetags);

This sorts the values in the alphabetic order

sort($autocompletetags, SORT_REGULAR);