How to make search.php return results that are more relevant

Question

For pset8 in search.php, I cannot get relevant results to show up. I am using match () against () and I really want to only use this. The problem is, for example, when I type in Cincinnati, I get Cincinnati, Iowa as a result. I have been trying to come up with a SQL solution to solve this and I have found a couple relevant questions, but none of the solutions are working.

There are two problems I am running into. First of all, for example, when I type in "Cincinnati", "Cincinnati, IA" is the first result and obviously I would prefer "Cincinnati, OH" to be the first result.

The other problem is when I type in, for example, "Boston, MA", the first results are, "New Boston, MA", "New Boston, NH", "Boston, NY" and more similarly irrelevant results.

Here Is the code.

if(is_numeric($_GET["geo"])){
    $places = CS50::query("SELECT * FROM places WHERE postal_code LIKE ?", $_GET["geo"]);
}
else{
    // Modify queries
    $queries = explode(" ", $_GET["geo"]);

    foreach($queries as &$query){
        $query = "'+" . $query . "*'";
    }

    $queries = implode(", ", $queries);

    // Get data from MySQL
    $places = CS50::query("SELECT * FROM places WHERE 
        MATCH (place_name, admin_name1, admin_code1) 
        AGAINST (? IN BOOLEAN MODE)",
        $queries);
}

Do you guys have any suggestions? I have tried a lot of alternatives, but this is the best code I have come up with.

Answer 1

It's as simple as turning this

and obviously I would prefer "Cincinnati, OH" to be the first result.

into computer code :) Kinda moves into the realm of computer learning/intelligence.

First you have to define "relevance" to the computer, and then tell the computer how to apply that "relevance".

Here's one possible approach, assuming "relevance" is "size" (and because I 💜 sql :)

$rows = CS50::query("Select count(postal_code) as zips, 
postal_code,place_name, admin_code1,latitude ,  longitude 
from places 
where  MATCH (postal_code, place_name, admin_code1 ) 
AGAINST (? IN BOOLEAN MODE) 
GROUP by place_name, admin_code1
Order by zips desc",$waldo."*");

It assumes that the bigger the place, the more zip codes there are. My indexes and variable names are different than yours, I hope you get the idea.

This also happens to solve the second problem at least for Boston, but that's more coincidence than by design. I had spent time on the "second problem" too, but there didn't seem to be any quick solution. It is very dependent on how fulltext searches work in mysql. I couldn't see a way beyond parsing user input for white space. So I chose GEPO, "Good Enough, Proceed Onward".