Full text search with mysql php

Question

I am trying to make a search key feature. But I am not getting any result with the following query.

public function SearchKey($key,$userid)
{
     $key = mysqli_real_escape_string($this->db, $key);
     $userid = mysqli_real_escape_string($this->db, $userid); 
      
      $query = mysqli_query($this->db,"SELECT * FROM posts WHERE 
       MATCH(theKey) AGAINST('$key' IN NATURAL LANGUAGE MODE) 
       AND uid = '$userid' ORDER BY sgq_id LIMIT 5") or die(mysqli_error($this->db));
      
       while($row=mysqli_fetch_array($query)) { 
          $data[]=$row;
       }
       if(!empty($data)) { 
         return $data;
       }   
     
}

Then fetch,

$search = $Data->SearchKey($key, $userid);
if($search){
   foreach($search as $data){
       echo $data['theKey'];
   }
}

For example if I search OK005 then I can not get any results. I tried Full-text Search functions https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Anyone can help me here, what I am missing ?

Answer 1

You're using single quotes to pass your variables. These will not be expanded in your query. You're better off using a prepared statement, and use parameter/value bindings to pass the variables. This will also solve the problem of SQL injection that your code appears to be vulnerable to.

You can try something like:

// Replace comment with appropriate connection data.
$pdo = new PDO(/* your DSN etc. */);

// Your query.
$sql =
'SELECT * FROM posts WHERE ' .
'MATCH(theKey) AGAINST(? IN NATURAL LANGUAGE MODE) ' .
'AND uid = ? ORDER BY sgq_id LIMIT 5';

// Create prepared statement from query.
$statement = $pdo->prepare($sql);

// Bind the values and enforce data type.
$statement->bindValue(1, $key, PDO::PARAM_STR);
$statement->bindValue(2, $userid, PDO::PARAM_INT);

// Run query.
$statement->execute();

// Get query results.
$rows = $statement->fetchAll();

// Your magic ...