Jump to content

Archived

This topic is now archived and is closed to further replies.

Jessica

Like and \\\' problem?

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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"?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Why does the actual data have escape characters in it?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.