PSET8 search LIKE and MATCH SQL

Question

I didn't understand if we had to implement both techniques or just one, but for fun I tried to implement both.

With the LIKE method, I wrote this code:

 $geo = $_GET["geo"];

    //match with LIKE
    function like($p1, $p2)
    {
        $temp = CS50::query("SELECT * FROM places WHERE ? LIKE ?", $p1, $p2 . "%");
        //if (count($temp) > 1)
            return $temp;
        //else return false;
    }

    $postal = like("postal_code", $geo);
    $place = like("place_name1", $geo);
    $name = like("admin_name1", $geo);
    $code = like("admin_code1", $geo);

      if ($postal != false)
        $places[] = $postal;

    if ($place != false)
        $places[] = $place;

    if ($name != false)
        $places[] = $name;

    if ($code != false)
        $places[] = $code;

But it doesn't work and I don't know why. I think the problem might be in the function declaration, but I don't know what's wrong and I'd appreciate if someone can explain me the error.

Also, I implemented the MATCH AGAINST method

//match with MATCH AGAINST
$places[] = CS50::query("SELECT * FROM places WHERE MATCH (postal_code, place_name, admin_name1, admin_code1) AGAINST (?)", $geo);

But this code returns an "array of arrays", so when the typeahead plugin tries to read the data it can't. I would also appreciate if someone can point out the error here. Thanks!

Answer 1

The problem with the first sql is because of the action mentioned in this comment in CS50.php [emphasis added]:

// replace placeholders with quoted, escaped strings

You could add a var_dump($query) to CS50.php after line 346 and you will see the query that is being run. Then try to run that query in phpMyAdmin. It has "quoted" the parameter name, something like SELECT * FROM places WHERE 'postal_code' LIKE '07003'. And that won't produce results. Since the first argument to query is "just a string", there are other ways to build it dynamically. Hint. Hint.

The problem with the second sql is this $places[]. The query will return an array, and you have told it to put that array into another array (by adding []), so you end up with an "array of arrays".