0

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:

enter image description here

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.

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

4
  • Thanks a bunch - turns out they weren't the same, with the query generated from the URL containing an extra space. This threw off the rest of code, which I've now solved. This all stemmed from my misunderstanding of the 'geo' input - I hadn't realised + was equivalent to a space. Thanks again for the help! Oct 24 '16 at 17:21
  • Actually, you seem to be full-text indexing on too many columns. You only need postal_code, place_name, admin_name1
    – Darc Nawg
    Oct 24 '16 at 17:42
  • @DarcNawg that may be true for your implementation. It is definitely not true for everyone's implementation. Oct 24 '16 at 17:45
  • True. Just trying to give a tip. I got a perfect score on pset 8 :)
    – Darc Nawg
    Oct 24 '16 at 17:50

You must log in to answer this question.

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