Search.php: using match in query only works in some cases

Question

I've taken the approach of using MATCH () AGAINST() in search.php:

require(__DIR__ . "/../includes/config.php");

// numerically indexed array of places
$places = [];

// define for scope
$query_elements;
$query_string = "SELECT * FROM places WHERE MATCH(postal_code, place_name, admin_name1, admin_code1, admin_name2) AGAINST ('";

// check on what character to explode the geo value
if (strpos($_GET["geo"], ",") !== false)
{
    $query_elements = explode(",", $_GET["geo"]);
}
else
{
    $query_elements = explode("+", $_GET["geo"]);
}

// iterate over words passed in, concatenating to dynamically create a query
foreach ($query_elements as $element)
{
    if (strpos($element, "+") !== false)
    {
        $query_string = $query_string . " $element*";
    }
    else
    {
        $query_string = $query_string . " +$element*";
    }
}
$query_string .= "' IN BOOLEAN MODE)";

// perform the query itself
$places = CS50::query("$query_string");

// output places as JSON (pretty-printed for debugging convenience)
header("Content-type: application/json");
print(json_encode($places, JSON_PRETTY_PRINT));

If I visit a URL like https://ide50-username.cs50.io/search.php?geo=Cambridge,Massachusetts I get the expected JSON data and no more. My issue arises when I use geo = "Cambridge,+Massachusetts" or "Cambridge+Massachusetts".

Instead of getting the rows that match both Cambridge and Massachusetts, I get the rows that involve either of them in some way. This looks like this.

I don't know how to resolve this, because I don't know where my issue lies - when I run the code in something like phpfiddle and run the query it outputs on the database manually, I get the correct results:

Cambridge,+Massachusetts as an input leads to the query:

SELECT * FROM places WHERE MATCH(postal_code, place_name, admin_name1, admin_code1, admin_name2) AGAINST (' +Cambridge* +Massachusetts*' IN BOOLEAN MODE)

When I run this on my database, I get the following:

Which is correct, but this isn't what happens when I visit the relevant URL. Does anybody know why the same thing is giving two different results, and how to fix it? The only solution I see at the moment is changing any plus signs to commas, however that seems to be treating the symptoms and not the cause.

Thanks for any help in advance.

Answer 1

All things being equal, running the exact same query will produce the same results. You need to examine the value of query_string more closely to determine if it matches the "working" query. Add a var_dump($query_string), then copy/paste the created query into phpMyAdmin to see the result. (Spoiler, I created a small repro case and neither input created the exact query).