Search code examples
phpmysqlsql-like

MySQL issue with 'Like' Query

I am having a problem with my query that includes a 'like' statement.

Here is my code:

function get_number_of_locations_by_zip_tag($zip, $tag) 
{
        global $db;
        $query = "SELECT * FROM location WHERE zip = :zip AND disabled = 0 AND (tags LIKE :tag OR name LIKE :tag) LIMIT :start, :number";

        $statement = $db->prepare($query);
        $statement->bindValue(':zip', $zip);
        $statement->bindValue(':tag',  '%'.$tag.'%', PDO::PARAM_STR);
        $statement->execute();
        $locations = $statement->fetchAll(); //fetch();
        $statement->closeCursor();
        return $locations;
}

I'm expecting the query to return the rows where the tag matches either the 'name' or the 'tags' field, but instead it will only return rows where the tags match.

If I switch the query to:

$query = "SELECT * FROM location WHERE zip = :zip AND disabled = 0 AND (name LIKE :tag OR tags LIKE :tag) LIMIT :start, :number";

Then the result is opposite, where it will only return the rows where the name field matches, and ignores the tags field.

Does anybody see anything wrong with this query? It works as I expected it to on my XAMPP server on Windows when I started developing, but when I switched everything over to my LAMP server, this query no longer worked correctly.

Solution

  • Well I did end up solving this a few weeks after I asked, and I thought I already posted my solution, but I guess not..

    According to all documentation that i've read it seems like DhruvPathak's answer should have worked, but for whatever reason, it didn't work for me. Instead I kind of cheated and did this:

    $query = "SELECT * FROM location WHERE zip = :zip AND disabled = 0 AND (name LIKE :tag OR tags LIKE :tag2) LIMIT :start, :number";

    Instead of trying to use the single bind value twice I seperated it into two variables and then bound them both with the same value:

    $statement->bindValue(':tag',  '%'.$tag.'%', PDO::PARAM_STR);
$statement->bindValue(':tag2',  '%'.$tag.'%', PDO::PARAM_STR);

    If anyone has actually hit this problem, I hope this solves it for you too.