Mini php search engine, order with the search term

[oz11]

Hey guyz, me again.

 

Basically i wrote a mini search engine in php, the problem is that i'm not sure how to order the query so that when searching say "google news", so that those with the 'title' "Google" are at the top.

 

The code:

<style>
ul {
  list-style-type: none;
  margin: 0;
  padding: 0;
}
</style>

<form action="" method="GET">
  <label for="search">Query:</label>
  <input type="search" id="search" name="s" value="<?php echo $_GET['s']; ?>"><br>
  <input type="submit" value="Submit">
</form> 
<?php
include 'includes/config.php';

if(!empty($_GET))
{

    $search_string = $_GET['s'];
    $searchTerms = explode(' ', $search_string);
    $searchTermBits = array();
    foreach ($searchTerms as $term) {
        $term = trim($term);
        if (!empty($term)) {
            $searchTermBits[] = "keywords.word LIKE '%$term%'";
        }
    }

   print_r($searchTermBits);
   echo "<hr>";
   echo implode(' AND ', $searchTermBits);


    $query = $_GET['s'];

    $stmt = $pdo->prepare("SELECT links.title, links.URL, count(*) FROM links, link_word, keywords WHERE links.link_id = link_word.link_id AND link_word.key_id = keywords.key_id 
    AND ".implode(' OR ', $searchTermBits)." OR links.title LIKE ? GROUP BY URL");
    $stmt->execute(['%'.$query.'%']); 
    
    echo "<ul>";
    while ($row = $stmt->fetch()) {
        echo "<li><a href='".$row['URL']."'>".$row['title']."</a><li><small style='color: grey;'>".$row['URL']."</small>";
    }
    echo "</ul>";
}
?>

At the moment its in second place, but i need these close matches to be first.  Help plz.

 

[requinix]

You need to rank the results, not just check if they contain the words in the query. Try a FULLTEXT search using MATCH AGAINST.
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

[oz11]

Thanks. I think i'm getting there but i seem to be at a place which feels as homage as before.

 

 

<style>
    ul {
        list-style-type: none;
        margin: 0;
        padding: 0;
    }
</style>

<form action="" method="GET">
    <label for="search">Query:</label>
    <input type="search" id="search" name="s" value="<?php echo $_GET['s']; ?>"><br>
    <input type="submit" value="Submit">
</form>
<?php
include 'includes/config.php';

if (!empty($_GET)) {
    $query = $_GET['s'];
    $stmt = $pdo->prepare("SELECT title, URL, word FROM links, link_word, keywords WHERE links.link_id = link_word.link_id AND link_word.key_id = keywords.key_id AND MATCH(word) Against(?) OR MATCH(title) Against(?) GROUP BY URL");
    $stmt->execute([$query,  $query]);
    echo "<ul>";
    while ($row = $stmt->fetch()) {
        echo "<li><a href='" . $row['URL'] . "'>" . $row['title'] . "</a><li><small style='color: grey;'>" . $row['URL'] . "</small>";
    }
    echo "</ul>";
}
?>

 Perhaps there is an operator i could use?

Edited September 13, 2022 by oz11
code

[Barand]

SELECT ... ORDER BY LOCATE('google', title) = 0

EG

mysql> select * from test;
+----------+
| title    |
+----------+
| Google   |
| Fox      |
| CNN      |
| BBC      |
| Google 2 |
+----------+
5 rows in set (0.00 sec)

mysql> select * from test order by locate('google', title) = 0, title;
+----------+
| title    |
+----------+
| Google   |
| Google 2 |
| BBC      |
| CNN      |
| Fox      |
+----------+
5 rows in set (0.02 sec)