Splitting keywords by space and searching MySQL database
I am working with PHP and MySQL. Below is a simple table:
------------------------------------------------------
| id | text |
------------------------------------------------------
| 1 | The quick brown fox jumped over the lazy dog |
------------------------------------------------------
I would like users to be able to search for keywords separated by spaces. I have a simple SQL query for the above which is SELECT * FROM table WHERE text LIKE %[$keyword]%
, which means that if I search for "Bull-fox" I won't get any results. Is there a way to separate the keywords with spaces, so if I search for "Bull-fox" it will run 3 searches. One for "The", one for "quick" and one for "fox" - removing all white spaces. Although it should only display one result as it belongs to the same row instead of 3, since all 3 keywords match the data in the row.
What's the best way to do this? Any suggestions to make it better are always welcome. Thank!
[EDIT]
Just thinking about it, would you separate the keywords by comma (,) the best option?
source to share
You can think of regular expressions throughREGEXP
to separate the words in a group or a group.
SELECT *
FROM tbl
WHERE
LOWER(`text`) REGEXP '\b(the|quick|fox)\b'
Matches:
- The swift brown fox jumps over the lazy dog.
- Quickly, get the fox!
- I ate cake
Does not match
- Brown dogs
Inside PHP, you can build this expression by splitting the search string into spaces and returning it back to |
after escaping each component.
$str = "the quick brown fox";
$kwds = explode(" ", $str);
$kwds = array_map("mysql_real_escape_string", $kwds);
$regexp = "\b(" . implode("|", $kwds) . ")\b";
Then use REGEXP '$regexp'
in your application.
Adding:
Since you didn't mention this in the OP, I want to make sure you know the MySQL full-text search capabilities of MyISAM tables if it might suit your needs. From your description, the full text doesn't sound like your requirement, but you should consider it as a possibility: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
source to share
I did it with a different approach to query string concatenation.
// this string contains four words that are used for searching. $ str = "quick brown fox"
// the query string is written in small chunks stored in the "$ uquery" variable
$ uquery = "select * from tablename where";
// the new variable "$ keywords" will contain values as an array
$ keywords = explode ("", $ str)
$ keywords = array_map ("mysql_real_escape_string", $ keywords);
// "$ k" will store the total. the elements in the array must contain "4" for this example
$ k = count ($ keywords);
// variable definition "$ i" is equal to zero
$ i = 0;
// start a while loop which will continue 4 times since in this example we only have 4 keywords
while ($ i <= $ k) {
// another new "$ uquery_" variable appears here, the next part will be concatenated with the previous "$ uquery" chunk later on
$ uquery_. = "theme like convert (_utf8 '% $ keywords [$ i]%' USING latin1)";
// for a more detailed explanation, let me tell you how "$ keywords [$ i]" will help in the above code snippet, for each value of "$ i" it will extract a keyword from an array, see below: Vacation Rentals / / $ keywords [0] = "the" for $ i = 0
// $ keywords [1] = "fast" for $ i = 1
// $ keywords [2] = "brown" for $ i = 2
// $ keywords [3] = "fox" for $ i = 3
// now concatenate the boolean && operator after each loop into "$ uquery_", but not for the last round, since the query must be completed after the last keyword
if ($ i! = $ k) {$ uquery_. = "& &"; }
// add from 1 to $ i after each round $ i ++; }
// now our main variable "$ uquery" is concatenated with "$ uquery_"
$ uquery. = "$ uquery _";
The above code snippet will generate the following query:
select * from tablename where topic like convert (_utf8 '% the%' USING latin1) && & && & topic like convert (_utf8 '% quick%' USING latin1) && & & topic like convert (_utf8 '% brown%' USING latin1) && & a theme like convert (_utf8 '% fox%' USING latin1)
Note. "subject" should be the name of a column in the mysql table, you can replace it with the column name as defined in your table.
Hope this helps a few people. If you have any questions, feel free to ask me through the Live Chat feature on my website http://www.79xperts.com
respectfully
Adnan Said
source to share