Extract all words from text field in mysql

Question

I have a table that contains text fields. In those fields I store text. There are around 20 to 50 sentences in each field depending on the row. I am making an auto-complete HTML object with HTML and PHP, and I would like to start typing the beginning of a word and that the database return sentences containing those words (Like Microsoft office 2007/2010 navigation pane).

I need mysql to return those words or sentences as a separate result, so i can manipulate them further.

Example:

--------------------------------------------------------------------
| id    | title |content                                            |
--------------------------------------------------------------------
1 |  test 1    |  PHP is a very nice language and has nice features.
2 |  test 2    |  Spain is a nice country to visit and has nice language.
3 |  test 3    |  Perl isn\'t as nice a language as PHP.

I need mysql query to return following as different result:

1,"nice language"
1,"nice features"
2,"nice country"
2,"nice langugage"
3,"nicea a language"

Here is my sql query:

SELECT id, SUBSTR(content,POSITION('nice' IN content),50)
    FROM entries 
          MATCH (title,entry) AGAINST ('nice' WITH QUERY EXPANSION)

Answer 1

New Answer

OP is actually asking nothing to do with php and javascript - his question concerns doing string manipulation directly within MySQL.

String manipulation isn't really the main focus of a DBMS. When dealing with "words" in a fluid text sense, there's a lot of logic required to determine where the next word boundary is, and you don't want your database doing this really. Plus, any queries written to do this will probably be incredibly difficult to read.

It depends exactly what you are doing, but it's quite likely that a DB only approach will be slower because there will be more function calls: SQL functions are pretty limited.

And for re-usability and best practice, what if you wanted to change your database in the future to say MongoDB? You'd need to re-write the whole damned awkward query.

No, my suggestion would be to pull the whole value using standard MySQL into PHP, throw it into PCRE, very simple regex, job done. It's better to show what you're actually doing in your PHP code as it's more "intention revealing".

At least 33% of a developer's work is picking the right tool for the job. PHP is the right tool in this example.

Original Answer

You have included the tags php and javascript, so I'm guessing (although your question needs more clarification on this) that you obviously want this 'autocomplete' running client-side. So as a result, you have to get your data from server-side to client-side first.

Twitter Bootstrap has something really cool called Typeahead. This uses JavaScript to perform (what I think) you require: the example on that page shows how you can type a country and it'll auto-complete it for you. It looks like this:

How do you get this working? Include the required JavaScript file first, and then write your HTML.

Here's some from the source code of the bootstrap page so you can see how it works:

<input type="text" data-provide="typeahead" data-items="4" data-source='["Alabama","Alaska","Arizona","Arkansas","California"]'>

Can you see how the data-source attribute is the one that gives the typeahead the information you want? You want to connect to MySQL, grab your data, and shove these into the data-source array for the JavaScript to work with, as above.

So, on your page load, you connect to MySQL and you pull all the relevant strings you would like to be "auto-complete-able" from the Database. You then put these as new Data attributes for the typeahead, and that's pretty much it!

--

Edit: There's a fork of twitter bootstrap's typeahead that allows AJAX calls, so you could use this to perform the data retrieval asynchronously (if you can figure it out, I'd recommend this approach).