I have a single field search form (like google) which searches a Database table field containing Titles of Books. In the Database Query, I have the condition as CONTAINS. But if the Title is say “Wappler is the Best Development Platform” and I type in the search “Wappler Best” is won’t find it, but if I search say “Wappler is” then it would find it.
Is there a way to allow search of the keywords with being precise about the order which the user types them?
I think you might be looking for this:
Edit: Alternatively, a less efficient solution is to split the search string at white-space, and for each element (word) do the “contains” query, and then you merge the searches and remove the duplicates somehow
I think this method can work well. I don’t think it’s necessary to then:
What database platform are you using ?
If you’ve been fortunate enough to choose PostgreSQL, I would recommend using full text search with a trigram index. We had been using elastic previously. It works best when the text vectors are stored in physical columns with an index. This means you’d have to set up a stored procedure or database trigger to keep these text vector columns updated with newest value from the raw data.
I can guide you further if you’re using PG. mySQL doesn’t support it and I’m sure SQL server has it’s own variant
People rarely do. Wordpress has done a lot of damage in that front over the years.
Here’s a couple of ideas:
Hi @scalaris , I am using MySQL.
I was hoping it would be a lot simpler than this.
Jus to be able to search for keywords in a title without being order specific.
Just a thought, I am no expert of arrays etc but if we are using get or post is there a way we can seperate the keywords into an array or some sort of list that we can then have the query search against?
So in my expla e above if the user searches for “Wappler Best” in the server action can we format it so that it seperate it into an array (I am assuming that’s what an array is) and in the MySQL query we put the condition of equal array1 or array2 or array3 but limit it to say 10 keywords?
Reinventing the wheel is usually much harder than you’d imagine, particularly with FTS. I did a bit of digging and it looks like the later versions of mySQL have FTS capabilities.
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html
Following scalaris’ link, here’s a more generic link that lists all FTS methods (other than “ngram”):
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
Thanks @scalaris and @Apple, it’s exactly what I was looking for. Pretty powerful this Full text search and it have relevance as well.
I will need to create a custom query to search the table, something like what I have below.
SELECT Title
FROM table_name
WHERE MATCH (col_name)
AGAINST('+wappler +is +the +best' IN NATURAL LANGUAGE MODE)