0

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.

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".

3
  • This makes a lot of sense! However, I tried plugging it in with the wildcard with something like the following: SELECT count(postal_code) as zips, * FROM places WHERE MATCH (place_name, admin_name1, admin_code1) AGAINST (? IN BOOLEAN MODE) GROUP BY place_name ORDER BY zips desc", $query); and I cannot get it to work! I run into a SQL error
    – Sam
    Jul 6 '16 at 22:45
  • A syntax error, I presume. change the SELECT thus: SELECT *, count(postal_code) as zips. And if you do not group by place_name, admin_code1, all your Cincinnatis will be in one "bucket", and it will likely be IA. Jul 6 '16 at 23:42
  • You would be correct! It worked!
    – Sam
    Jul 7 '16 at 0:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .