Like and \\\' problem?

[Jessica]

SELECT name, id FROM all_items WHERE name LIKE '%pirate\\\'s%'
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0030 sec)

SELECT name, id FROM all_items WHERE name LIKE '%pirate\'s%'
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0030 sec)

SELECT name, id FROM all_items WHERE name LIKE '%pirate%'
The Pirate\'s Code  30
Famous Pirates 32

I need to be able to search for the entry "The Pirate\'s Code" using "pirate\'s" or "pirate\\\'s". Why do the first two not work?

[king arthur]

Have you got magic_quotes_runtime turned on? Because maybe the string in the database has been escaped twice and is actually "The Pirate\\\'s Code"?

[fenway]

I'm not sure I understand why the string in your DB has the backslash at all... sounds like it got "over-escaped" on the way in, as king arthur suggested.

[Jessica]

The actual record in the database is The Pirate\'s Code, and that's what I need to find. :(

Here's what happens. The user enters pirate's in a search form.

Then:

$item = mysql_real_escape_string(strip_tags($_POST['search']));
$shop_info = "SELECT name, id FROM all_items WHERE name LIKE '%$item%'";

At that point $shop_info = "SELECT name, id FROM all_items WHERE name LIKE '%Pirate\\\'s%'"
Which if I understand the escaping, it should find pirate\'s.

And, I have no idea why it's overescaped, when I put it in the database I used "mysql_real_escape_string(strip_tags($_POST['search']));" to get the string and put it in.

ARGH

[fenway]

Why does the actual data have escape characters in it?

[Jessica]

[quote author=fenway link=topic=103135.msg410746#msg410746 date=1154881440]
Why does the actual data have escape characters in it?
[/quote]

And, I have no idea why it's overescaped, when I put it in the database I used "mysql_real_escape_string(strip_tags($_POST['item']));" to get the string and put it in.

In that case, 'item' was The Pirate's Code.

Now I've gone into the database and changed the entry to "The Pirate's Code" unescaped, and the query
SELECT name, id FROM all_items WHERE name LIKE '%Pirate\\\'s%'

Finds it.

WTF is going on here? Something is wrong with my database settings.

[fenway]

Sounds like you have magic quotes turned on, and then you're running escape string again.  You need to have a wrapper function that handles this... there are many such examples.  However, if it's stored "properly" now in the DB, you should need to include the extra backlash in your query.