SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can "Some String" != "Some String" ?

    I'm building a website that allows members to upload images.
    The images themselves are not stored in the database - just the image information
    (id, title, filename, thumbnail, date, ownerid).

    I created small test tables in PHPMyAdmin, and everything was working fine until I got my image upload script working.
    Apparently, SQL doesn't recognize strings that are inserted by my script.

    In other words:
    Code:
    SELECT * FROM 'image'
    Returns all records
    Code:
    SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
    Returns the row with "Some Title" if it was inserted by PHPMyAdmin.
    Code:
    SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
    Returns 0 records if "Some Title" was inserted through my PHP script.
    Code:
    If I copy and paste or type the words "Some Title" in PHPMYAdmin:
    SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
    Now returns the correct data.
    It *seems like I didn't change anything, and it still looks like the same string to me, but to SQL
    "Some Title" != "Some Title" for some mysterious reason.
    Incidentally, all of the other rows with strings have the same issue.

    Here's what I've tried:

    Code:
    SHOW CREATE TABLE:
    CREATE TABLE `image`
    (`id` int(11) NOT NULL AUTO_INCREMENT,
     `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
     `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
     `thumbnail` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
     `date` date NOT NULL, `ownerid` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`))
    ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    Code:
    SHOW CREATE DATABASE:
    CREATE DATABASE `imagedb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */
    Code:
    Of course, when I connect to my database, I always run:
    !mysqli_set_charset($link, 'utf8')
    Code:
    All of my html and php pages contain:
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    Code:
    Someone suggested putting this at the top of my script:
    ini_set('default_charset', 'UTF-8');
    It didn't help, so I removed it.
    I've already tried going through each table with PHPMyAdmin and
    setting the collation for each column manually.

    Code:
    I also tried running this script:
    SET character_set_results = 'utf8', character_set_client = 'utf8', 
    character_set_connection = 'utf8', character_set_database = 'utf8', 
    character_set_server = 'utf8'
    Maybe it's not an encoding (collation) problem after all...
    But I'm stumped, so any help would be appreciated!

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try removing the single quotes from the table and column names

  3. #3
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good idea - thanks Kalon. Unfortunately, I got the same result.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    If you search for a complete string, why use LIKE?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    do a binary compare and you will see whether the strings actually are equal

    ... WHERE 'Some string' = BINARY 'Some string'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your suggestions Guido and Rudy!
    It looks like it was 2 problems: Encoding, and extraneous spaces.

    When I tried using LIKE "%Some Title%" before, it didn't help.
    So I went back to "=", but just left LIKE in the query since I was copying and pasting,
    and without "%%" it means essentially the same as "=".

    So I just tried LIKE "%Some Title%", and voila! But why?
    They *should* be equal, not similar.

    Looking closer, I see that my insert query is inserting extraneous spaces into my table.

    So LIKE "%Some Title%" didn't work originally because something was wrong with the encoding.
    At some point, I fixed the encoding problem (see above), but didn't notice because I was using "LIKE" (as in "=").
    So I was comparing " Some Title " with "Some Title" which are definitely not equal.

    Now I just have to figure out where those spaces are coming from...

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try using $inputstring = trim($inputstring) in your php before making the insert, to remove any spaces. Ditto when doing an search from an input form.

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by BananaBanshi View Post
    Now I just have to figure out where those spaces are coming from...
    if the strings are from user inputs, it's quite possible the user accidentally (or even deliberately for some reason) entered them.

    you can get rid of leading and trailing spaces using trim() as Dr John suggested.

  9. #9
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dr John, and thanks again Kalon. I am now using trim for my user inputs - I'm sure it will save a lot of trouble in the future.


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
  •