MySQL issue with 'Like' Query

Question

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.

Please don't add signatures or taglines to your posts.

user229044
– user229044 ♦

2011-09-13 05:28:36 +00:00
Commented Sep 13, 2011 at 5:28 — user229044
– user229044 ♦, Commented Sep 13, 2011 at 5:28

DhruvPathak · Accepted Answer · 2011-09-13 05:31:21Z

1

You might want to change :

tags LIKE :tag OR name LIKE :tag

to

tags LIKE ':tag' OR name LIKE ':tag'

Or change in your code :

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

As like expression should be in quotes .eg name LIKE '%john%'

answered Sep 13, 2011 at 5:31

DhruvPathak

43.4k17 gold badges125 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OrangeGrover Over a year ago

I just tried both of these solutions, and it doesn't seem to return any rows now, but when I run the following command from the mysql command line: select * from location where zip = 92346 and disabled = 0 and (name Like '%Am%' or tags like '%Am%'); It's able to return both fields, so somehow the binding isn't translating into that command.

OrangeGrover · Accepted Answer · 2011-12-16 09:21:04Z

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.

Collectives™ on Stack Overflow

MySQL issue with 'Like' Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related