Alphabetize when some data contains single quote?

[fodahe]

I am trying to pull names from a table one at a time in alphabetical order. The query I use looks like this:

SELECT first_name FROM names WHERE first_name > '$currentName' ORDER BY first_name ASC LIMIT 1

This works fine except for names that contain a single quote. For example the name Andre would work, but Andre' would not (in my database it is escaped Andre\'). For some reason it doesn't find them at all. I have looked everywhere and cannot find a good way to do this. Any help would be appreciated.

[r937]

you've got a php problem, not a mysql problem

if you've actually stored the backslash-quote, that's another problem (also due to php, probably

[risoknop]

1) Don't use addslahes() for inserting data into database. That's probably causing the \ characters. Use mysql_real_escape_string() or better prepared statements with MySQLi or PDO.

2) Your query looks alright, it's probably PHP problem. Post the code you use to insert and retrieve data.

[fodahe]

That's the strange thing, I do use mysql_real_escape_string. When I use this code on my windows machine everything works fine. The problem occurs when I upload the code to my host. Here is how I enter the data:

First I catch the name from a post.
$fname = mysql_real_escape_string($_POST['fname']);

Then I put it into the database.
mysql_query("INSERT INTO names (first_name) VALUES ('$fname') ");

Doing that adds the slashes on my host's server, but does not on my windows. So is there something wrong with that code? Or is there some other issue going on with the host PHP?

Thanks for taking the time to help.

[fodahe]

I figured this problem out finally, but just in case this helps anyone else out I will post what was going on.

The magic_quotes was enabled and causing the extra \ to be placed on the queries. I had to disable the magic_quotes in the php.ini file and then restart the server. The php.ini file should look like this:

; Magic quotes
;

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off

; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off

; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off

Thanks for everyone's help.

[risoknop]

Yeah, magic_quotes is like the worst thing ever. It's been turned off by default since version 5 I believe. Are you still running PHP4?