1

I have this query which searches articles in a db. I would like to change the query so that if the keyword is found in the "title" row, then the query should sort by that first - in order to put it in the top of the results.

Results with the keyword in the title should be considered more important than if the keyword is in the content of an article.

Can this be achieved in the query itself, or does it require some sort of algorithm?

Here's my query:

if (isset($_POST['search'])) {
  $search = filter_var($search, FILTER_SANITIZE_SPECIAL_CHARS);

  $sql = "SELECT * FROM mydb WHERE title LIKE :search OR description LIKE :search OR content LIKE :search ORDER BY dates DESC";

  $stmt = $pdo->prepare($sql);
  $stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
  $stmt->execute();
    if ($stmt->rowCount() > 0) {
        $result = $stmt->fetchAll();
        foreach($result as $row) {
          // Loop...
        }
    }
    else {

    }

  // Closing
  $stmt = null;
  $pdo = null;
}
Meek
  • 3,086
  • 9
  • 38
  • 64
  • In order to achieve this you need some value returned from comparison of each each columns, and obviously that is straight forward approach. You can use Levenshtein distance to do so. [link](https://stackoverflow.com/questions/634995/implementation-of-levenshtein-distance-for-mysql-fuzzy-search) Once you have implemented you can take max of those values and apply sorting on that column – Abhishek May 23 '18 at 12:34
  • Looks like a duplicate of [MYSQL advance searching - order by with LIKE operator](https://stackoverflow.com/q/46421200/2943403) – mickmackusa Apr 24 '23 at 04:57

1 Answers1

-1

So, it seems that this one has a simple solution after all - just order by title LIKE :search:

$sql = "SELECT * FROM mydb WHERE title LIKE :search OR description LIKE :search OR content LIKE :search ORDER BY title LIKE :search DESC";
Meek
  • 3,086
  • 9
  • 38
  • 64