Search fields for the whole word, and nothing but the word

Question

I have a PHP keyword search interface that works with a database (MySQL) that has a Keywords field.

The way the keyword field is set up is as follows: this is a varchar with all the words formatted as shown below ...

there, theyre, them, warm, etc.

if I want to simply return the exact word "the" from the search, how will this be achieved?

I tried using 'the%' and '%the' in PHP and it does not work without returning all of the lines where the keyword is displayed.

Is there a better (more accurate) way to do this?

thanks

Answer 1

If you want to select rows with exactly the the keyword:

SELECT * FROM table WHERE keyword = 'the'

If you want to select strings that have the the keyword anywhere :

SELECT * FROM table WHERE keyword LIKE '% the%'

If you want to select the lines to run using the the keyword:

SELECT * FROM table WHERE keyword LIKE 'the%'

If you want to select end strings with the the keyword:

SELECT * FROM table WHERE keyword LIKE '% the'

Answer 2

try it

 SELECT * FROM tablename WHERE fieldname REGEXP '[[:<:]]test[[:>:]]' 

[[:<:]] and [[:>:]] are markers for word boundaries.

MySQL regular expressions

Answer 3

if you are also looking for a comma, you can be sure to get the whole word.

 where keywordField like '%, the, %' or keywordField like '%, the' or keywordField like 'the, %' 

Answer 4

Perhaps I did not understand the question correctly ... but if you need all the words where "the" appears, LIKE "% word%" should work.

If the word DB HUGE MySQL may not get some of the words that can be solved in two ways ...

1- get databases that support large sizes (not many ppl would choose this one tho). For example, SQL Server has a CONTAINS function that works better than LIKE "% word%".

2- use an external search tool that uses an inverted index search. I used Sphinx for the project and it works pretty well. This is best if you rarely update the data rows from which you want to look for what should be. For example, Sphinx will generate a file from your MySQL table and use this file to solve the problem (very fast), this file should be reindexed every time you insert or update the table, which makes it a much better solution if you rarely update or insert new ones strings.

Answer 5

It looks like you have one, many relationships happening inside a column. Perhaps it would be better to create a separate table for keywords with a row for each keyword and foreign key in everything you are looking for.

It seems that "% ???%" is usually a bad idea, because the database cannot use the index to scan the entire table. Whether it will depend on the size of the data you are working with, but it is worth considering in advance. The only best way to help database performance is in the initial table design. This can be difficult to change later.