How to make FULL TEXT SEARCH more accurate using PHP

Question

I am implementing a search feature for my project. I am using a FULL TEXT SEARCH query to derive accurate results to User. I am beginner in PHP programming and I do not have enough information about FULL TEXT SEARCH.

This is my query:

$sql = $conn->prepare("SELECT *, MATCH(title, keyword) AGAINST(? IN BOOLEAN MODE) AS relevance FROM table ORDER BY relevance DESC LIMIT 20");
$sql->bind_param("s", $q);
$sql->execute();
$rs = $sql->get_result();

This query works good but this is only showing old results first instead of accurate results, and second thing is this query is not working correctly when the length of keyword is not more than 1 (e.g. keyword = Google).

Please do not give suggestions about Elastic search, Sphinx, Algolia etc.

Does this answer your Question https://stackoverflow.com/questions/37711370/mysql-how-to-get-search-results-with-accurate-relevance — KUMAR, Jul 22 '20 at 05:52

score 1 · Answer 1 · answered Jul 25 '20 at 13:04

1

When MATCH() is used in a WHERE clause, the rows returned are automatically sorted with the highest relevance first.
So all you have to do is, remove the match from select and put it in where condition.
Source: https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html

answered Jul 25 '20 at 13:04

Akshay Vanjare

655
3
10

Can you tell me what is wrong with my Code above and there is no `WHERE` clause in my `SQL` query – Amaan warsi Jul 25 '20 at 15:22
Please change your query as below: "SELECT * FROM table WHERE MATCH(title, keyword) AGAINST(? IN NATURAL LANGUAGE MODE) LIMIT 20" – Akshay Vanjare Jul 25 '20 at 15:48
OK I'll try this – Amaan warsi Jul 25 '20 at 15:56
Now I'm getting this error `Fatal error: Call to a member function bind_param() on boolean` can you help me to solve this I also changed my table name – Amaan warsi Jul 25 '20 at 16:03
1

That means you have error in your sql query. First try running query in mysql client. You can also check this example to find error: https://www.php.net/manual/en/pdo.errorinfo.php#refsect1-pdo.errorinfo-examples – Akshay Vanjare Jul 25 '20 at 16:21
I get error and I solve but the main problem not solved the results are not sorted in relevancy – Amaan warsi Jul 25 '20 at 16:57
Can you share the search query and result? – Akshay Vanjare Jul 26 '20 at 04:51
Yes I'll share you – Amaan warsi Jul 26 '20 at 06:51
See this http://alapdorl.epizy.com/forms/search?q=YouTube&s=Search – Amaan warsi Jul 26 '20 at 08:04

aryanknp · Answer 2 · 2020-07-28T10:05:52.097

1

Why are you not using the sql like operator, I am providing you the example for multiple words in column named product in table named products

$db=mysqli_connect('localhost','root','','project');

 $search=$_GET['userinput'];
 $searcharray = explode(' ', $search);
  $searchpdo=array();
$finalstate="";

foreach ( $searcharray as $ind=> $query){
$sql=array();
$exp='%'.$query.'%';


     array_push($sql,"(title LIKE ?)");
    array_push($searchpdo,$exp);
array_push($sql,"(keywords LIKE ?)");
    array_push($searchpdo,$exp);

if($finalstate==""){
$finalstate = "(".implode(" OR ",$sql).")";
}
else{
$finalstate = $finalstate." AND "."(".implode(" OR ",$sql).")";
}
}



 $stmt = $db->prepare("SELECT * FROM products WHERE (".$finalstate.") ");
 
$types=str_repeat('s',count($searchpdo));
        $stmt->bind_param($types,...$searchpdo);
$stmt->execute();
$result = $stmt->get_result();

This will provide you the correct result with single word or multiple words

edited Jul 28 '20 at 10:05

answered Jul 28 '20 at 07:43

aryanknp

1,135
2
8
21

Can you update this code to match 2 rows like `title` **OR** `keywords` – Amaan warsi Jul 28 '20 at 09:37
2 rows or 2 columns? – aryanknp Jul 28 '20 at 10:03
Sorry 2 columns – Amaan warsi Jul 28 '20 at 10:04
Thank you for your such interest on my question and your answer working nicely but not powerful then `Full Text Search` – Amaan warsi Jul 28 '20 at 10:13
I mix your code with full text in my project and getting good results – Amaan warsi Jul 28 '20 at 10:27
1

This is vulnerable to SQL injection. Never build dynamic query (i.e. building SQL query string dynamically) using user inputs as it is hard to sanitize the user inputs properly. Always use parameterized queries using the database access layer library being used. See further discussion here https://stackoverflow.com/questions/4712037/what-is-parameterized-query – K4M Jul 30 '20 at 04:48
this is a prepared statement look before commenting – aryanknp Jul 30 '20 at 04:50
Also, a `LIKE` query would not be as efficient as `FULL TEXT SEARCH` because the latter can have indexes that are optimized for text search and can be performant on columns with large amount of text. Also feature-wise they are not comparable. The latter supports linguistic searches (thesaurus, prefixed term, etc.). They solve problems in different scales. – K4M Jul 30 '20 at 04:53
@K4M can you give your suggestions as answer format because my bounty is going to expire – Amaan warsi Jul 30 '20 at 18:37

score 1 · Answer 3 · answered Jul 29 '20 at 02:12

I think you have to tweak you query little bit and you would get desired results as under:

$sql = mysql_query("SELECT * FROM 
         patient_db WHERE 
         MATCH ( Name, id_number ) 
         AGAINST ('+firstWord +SecondWord +ThirdWord' IN BOOLEAN MODE);");

and if you want to do exact search:

$sql = mysql_query("SELECT * 
                  FROM patient_db 
                  WHERE MATCH ( Name, id_number ) 
                  AGAINST ('"Exact phrase/Words"' IN BOOLEAN MODE);");

I had also posted the same answer in SO post somewhere but didn't know the post

score 1 · Answer 4 · answered Jul 30 '20 at 19:46

There are multiple aspect to your question

If available, use mysql client to run the query instead of PHP first, until your query is ready to the like you want
If you recent documents (record) to show up on top of the search result, you need to change your ORDER BY clause. Currently, it is supposed to return the closest match (i.e. by relevance).

You need to strike a balance between relevance and recency (not clear how you define this) in your custom logic. A simple example that prioritize last week over last month and last month over the rest:

SELECT 
  ....
  , DATEDIFF (ItemDate, CURDATE() ) ItemAgeInDays
ORDER BY 
 relevance 
   * 100
   * CASE 
       WHEN ItemAgeInDays BETWEEN 0 AND 7 --- last week
       THEN 20
       WHEN ItemAgeInDays BETWEEN 0 AND 30 --- last month
       THEN 10
       ELSE 1
   END
 DESC

You say single word item cannot be searched. In BOOLEAN MODE, you build a boolean logic for your search and such it uses special characters for that. For example +apple means 'apple' must exist. It is possible your single word might be conflicting with these characters.

Please review this reference, it explains the BOOLEAN MODE in great detail.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

You say the query is not returning correct result. FULL TEXT search searches for your login in each document(row) and finds how many times it appears in each document. It then offset that by how many times your search appears in ALL documents. This means it prioritizes records where your search appears much more than the average. If your search is not distinguishing enough, it might seem not correct if most documents are similar to each in terms of that search. See the above link for more details.
BOOLEAN MODE does not sort the result by relevance by default. You need to add ORDER BY yourself, which you already did. Just wanted to note it here for others

How to make FULL TEXT SEARCH more accurate using PHP

4 Answers4