Search Query not accurate enough

Question

I have a search query done by me to the best of my knowledge in PHP but there are some improvements required:

1. When I search say 'what is food' and I have 'what is food' in the database all results containing one of the keywords 'what', 'is', 'food' are shown. The desired behaviour is to display results containing the exact phrase 'what is food' (first)

2. Only the last word in the query is highlighted and I want to highlight all words

Desired behaviour: The right answer shows at the top, regardless of its position in the database.

My current code is like this:

if (isset($_GET["mainSearch"]))
{
  $condition = '';
  $mainSearch = SQLite3::escapeString($_GET['mainSearch']);
  $keyword = $_GET['mainSearch'];
  $query = explode(" ", $keyword);
  $perpageview=7;

  if ($_GET["pageno"])
  {
      $page=$_GET["pageno"];
  }
  else
  {
      $page=1;
  }

  $frompage = $page*$perpageview-$perpageview;

  foreach ($query as $text)
  {
      $condition .= "question LIKE '%".SQLite3::escapeString($text)."%' OR answer LIKE '%".SQLite3::escapeString($text)."%' OR ";
  }
  foreach ($query as $text_2)
  {
      $condition_2 .= "bname LIKE '%".SQLite3::escapeString($text_2)."%' OR bankreq LIKE '%".SQLite3::escapeString($text_2)."%' OR ";
  }

  $condition = substr($condition, 0, -4);
  $condition_2 = substr($condition_2, 0, -4);


  $order = " ORDER BY quiz_id DESC ";
  $order_2 = " ORDER BY id DESC ";
  $sql_query = "SELECT * FROM questions WHERE " . $condition . ' '. $order.' LIMIT '.$frompage.','.$perpageview;
  $sql_query_count = "SELECT COUNT(*) as count FROM questions WHERE " . $condition .' '. $order;
  //$mainAnswer = "SELECT * FROM questions WHERE question LIKE '%$mainSearch%' or answer LIKE '%$mainSearch%'";
  $bank_query = "SELECT * FROM banks WHERE " . $condition_2 . ' LIMIT 1';
  $result = $db->query($sql_query);
  $resultCount = $db->querySingle($sql_query_count);
  $bankret = $db->query($bank_query);
  //$mainAnsRet = $db->query($mainAnswer);
  $pagecount = ceil($resultCount/$perpageview);

  if ($resultCount > 0)
  {
  if ($result && $bankret)
  {
      while ($row = $result->fetchArray(SQLITE3_ASSOC))
      {

          $wording = str_replace($text, "<span style='font-weight: bold; color: #1a0dab;'>".$text."</span>", $row['answer']);

           echo '<div class="quesbox_3">
            <div class="questitle">
                <h2>'.$row["question"].'</h2>
            </div>
            <div class="quesanswer">'.$wording.'</div>
        </div>';
      }
      while ($brow = $bankret->fetchArray(SQLITE3_ASSOC))
      {
            $bname = $brow['bname'];
            $bankbrief = $brow['bankbrief'];
            $bankreq = $brow['bankreq'];
            $bankaddress = $brow['bankaddress'];
            $banklogo = $brow['banklogo'];
            $founded = $brow['founded'];
            $owner = $brow['owner'];
            $available = $brow['available'];


           echo '<div class="modulecontent">
            <div class="modulename">
                <div class="mname">'.$bname.'</div>
                <div class="mlogo"><img src="'.$banklogo.'"></div>
            </div>';

            if (strlen($bankreq) > 300)
            {
                $bankcut = substr($bankreq, 0, 300);

                $bankreq = substr($bankcut, 0, strrpos($bankcut, ' ')).'... <a href="bankprofile.php?bname='.$bname.'">Read More</a>';
                echo '<div class="modulebrief">'.$bankreq.'</div>';
            }
            echo '<div class="modulelinks">
                <div class="mfound">Founded: <span>'.$founded.'</span></div>
                <div class="mowned">Ownd By: <span>'.$owner.'</span></div>
            </div>
        </div>';

               // <div class="mavailable">Available for Export Loan: <span>'.$available.'</span></div>
      }
      ?>
      <div class="page_num">
      <?php
      for ($i=1; $i <= $pagecount; $i++) {
         echo '<a href="searchresult.php?mainSearch='.$mainSearch.'&pageno='.$i.'">'.$i.'</a>';
      }
      ?>
      </div>
      <?php
  }
  }
  else
  {
      $session_n = $_SESSION['log_id'];
      $sesdate = date('d/M/Y');
      echo "<div class='searchNone'><p>No results found</p></div>
      <div class='sendSearchQ'>
      <p>Please send us your question.</p>
      <form action='sendquestion.php' method='post' encytype='multipart/form-data'>
      <div class='searchQinputs'>
          <input type='text' name='searchQuestion' id='searchQuestion'placeholder='Whats your question'><br>
          <input type='submit' name='sendQuestion' id='sendQuestion' value='Send'>
          <input type='text' name='user' id='user' value='$session_n' style='display: none'>
          <input type='text' name='qDate' id='qDate' value='$sesdate' style='display: none'>
          <input type='text' name='status' id='status' value='0' style='display: none'>
          </div>
      </form>
      </div>";
  }
}

Answer 1

Add a sortable field to your query

First we need to simplify your problem

Your problem can be treated as a simple keyword match, where the top results must match all keywords as entered.

A Search for: some search text should return all results containing any of those words ['some', 'search', 'text'], with results at the top matching exactly as entered "some search text".

This means you'll need to create an aggregate field that allows the result to be sorted based on a match. The only way I know to do this, without refactoring data and/or code, is with a MySql Case statement.

Your Query Simplified

SELECT *
FROM questions
WHERE
    question LIKE '%[word1]%' OR answer LIKE '%[word1]%'
    OR question LIKE '%[word2]%' OR answer LIKE '%[word2]%'
    OR question LIKE '%[word3]%' OR answer LIKE '%[word3]%'
ORDER BY quiz_id DESC

Case [full-match] and Sort

What we need to build is a query that looks a bit like this:

SELECT *,
    (CASE WHEN
            question LIKE '%[full-search-query]%'
            OR answer LIKE '%[full-search-query]%'
        THEN 1 ELSE 0
    END) as fullmatch
FROM questions
WHERE
    question LIKE '%[word1]%' OR answer LIKE '%[word1]%'
    OR question LIKE '%[word2]%' OR answer LIKE '%[word2]%'
    OR question LIKE '%[word3]%' OR answer LIKE '%[word3]%'
ORDER BY fullmatch DESC, quiz_id DESC

Adjusting your code

// your initial storage of the full search, before you split it on spaces
$keyword = $_GET['mainSearch'];

. . .

// build our sorting field
$sortFullMatch = "(CASE WHEN question LIKE '%".SQLite3::escapeString($keyword)."%' OR answer LIKE '%".SQLite3::escapeString($keyword)."%' THEN 1 ELSE 0 END) as fullmatch";

. . .

// adjust the query and sort
$order = " ORDER BY fullmatch DESC, quiz_id DESC ";
$sql_query = "SELECT *,". $sortFullMatch ." FROM questions WHERE ".$condition.' '.$order.' LIMIT '.$frompage.','.$perpageview;

What does this do?

We added a new field to the SELECT statement, fulltext. When a question or answer contains exactly the full search, this field will be 1, otherwise 0. Then simply sort on this field.

Highlighting

As for your highlighting issue, you're only replacing on $text which is set in a loop on each word in mainSearch. Thus, it will only be the last word in the set. Instead, you'll need to do a similar loop here.

Your Code

$wording = str_replace($text, "<span style='font-weight: bold; color: #1a0dab;'>".$text."</span>", $row['answer']);

Adjusted

foreach($query as $text) {
    $wording = str_replace($text, "<span style='font-weight: bold; color: #1a0dab;'>".$text."</span>", $row['answer']);
}