SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Alphabetize when some data contains single quote?

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •