basic query problem (apostrophe)

[boywonder]

Hi,
this should be simple I think to do it the right way, but it's giving me problems...

I have a form where people can search a members table in several ways, one of which is by typing in last name. So there's 2 members with last name "O'Neal"

The apostrophe was escaped prior to inserting so the names sit in the DB as "O\'Neal"

So... how do I find the match when someone types a search for "O'Neal"

seems to me one of the following should work but they don't...

SELECT * FROM members WHERE lastname = 'O\'Neal'
OR
SELECT * FROM members WHERE lastname = 'O\\\'Neal'

I can find the match by using one of the following:

SELECT * FROM members WHERE lastname LIKE 'O%Neal'
OR
SELECT * FROM members WHERE lastname LIKE 'O__Neal'

but it seems that I should not have to do that... How would I just search for the name without using _ or %

Thanks kindly for any insight.

[Kiwi]

The documentation says that "\'" will work. My suspiscion is that something else is causing the problem. I can suggest two things:
1. Echo the query string to the screen from PHP and see if that's the right string (and that php isn't escaping anything).
2. Copy the echoed string into phpMyAdmin and see if it works directly in the database.
Between the two, it should tell you if the problem is in PHP, MySql or something between the two.

[boywonder]

Kiwi thanks for responding.
I do echo the query string to the page as well as any errors that occur.

phpmyadmin was a good idea. I queried for the name and phpmyadmin found the matches with this syntax:

LIKE 'O\\\\\'Neal'

but that still didn't work in my PHP script. it echoed out 3 slashes for the query instead of 5 (and found no matches, just as my previous attempts). So I wound up with 9 slashes in my php code to get the 5 slash query above and the matches were found.

LOL what a nightmare, I still think I'm missing something or doing something wrong but no time to dwell on this ... for now I'll just stick with %

Thanks very much for your help.

[whammy]

Dunno about MySQL but in SQL Server you'd escape it like:

O''Neill