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.