Problem with querying text that has singlequote

[alfalf]

HEllo all.

I run into a problem with singlequotes and querying.
Help is appreciated, or if anyone have some expirience, this would be fine.

I have cities table (MySQL).
City names are sometimes like:
's-Hertogenbosch (this is a name of actual city in Netherlands) or
Saint David's (this is a name of actual city in Grenada).

A problem is when querying such city name in mysql table like:

"SELECT * FROM cities WHERE left(name,$length)= '$prefix' ORDER BY accent_name"

where $length is length of prefix inputed and $prefix is prefix inputed (PHP coding, $prefix for example is "s-Hert", or whole name such as "saint david's".

[red]Problem that I get is that if I use ' sign (singlequote), I receive no result from query, as I should.[/red]

This presents a problem.

Charset used for table is Latin 1.
I have no problems with locale specific characters (Swedish, Turkish, Japanese, Eastern European,...).
ONLY PROBLEM IS WITH SINGLEQUOTE.

Any tips or ideas? Thanks.

 

[TonyGroves]

The problem is that if $prefix equals "Saint David's" (and $length==13) for example, then the query text becomes:[tt]
SELECT *
FROM cities
WHERE left(name,13) = 'Saint David's'
ORDER BY accent_name[/tt]
As you can see, the number of single-quotes is unbalanced there, so causing a syntax error.

What you need to do is to transform the city name before inserting it into the query, by inserting before each single-quote a backslash (Saint David\'s) or another single-quote (Saint David''s).

 

[hoinz]

Hello,

it should work if you replace the ' in $prefix (input) by '' or \'

http://dev.mysql.com/doc/mysql/en/string-syntax.html

hope this helps

 

[alfalf]

Hello people, thanks.

As of Saint David's -> the problem is solved with \ (backslash - Saint David\'s worked just fine).

But for 's-Hertogenbosch (and other cities starting with singlequote), no result.

This is a bit paculiar problem but not so of huge importance. Never the less, any other suggestion will be appreciated, and thanks upper guys for instructions.

Thanks!

 

[TonyGroves]

The same system should work for \'s-Hertogenbosch or ''s-Hertogenbosch.