SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql where field=0 strange behaviour

    Hi,
    PHP Code:
    CREATE TABLE IF NOT EXISTS media(
        
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        
    contestant_id INT UNSIGNED NOT NULL,
        
    title VARCHAR(255NOT NULL,
        
    description VARCHAR(65535) DEFAULT NULL,
        
    uid VARCHAR(255NOT NULL,
        
    published DATETIME NOT NULL,
        
    visible TINYINT(1) DEFAULT 0 COMMENT '1 yes 0 no',
        
    iswinner tinyint(1) DEFAULT 0 COMMENT '1 yes 0 no',
        
    PRIMARY KEY (id),
        
    UNIQUE KEY unique_media_uid (uid)
    ENGINE=InnoDB DEFAULT CHARSET=utf8
    can anyone explain me why on earth with this query
    PHP Code:
    SELECT 
    FROM  `media
    WHERE uid =
    I get data ?

    in uid field I've got data like
    kHnn67 the youtube watch id.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you dump a few rows of data so we can test it ourselves?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here they are:
    PHP Code:
    INSERT INTO `media` (`id`, `contestant_id`, `title`, `description`, `uid`, `published`, `visible`, `iswinner`) VALUES
    (101'Video of .'NULL'PCHGsCvwgvg''2013-05-16 14:41:46'00),
    (
    112'Video of ..'NULL'LdeDXCKphpE''2013-05-16 14:44:34'10),
    (
    123'Video of ...'NULL'Mqkh6EgbBZc''2013-05-16 14:48:59'10),
    (
    131'Video of ....'NULL'8K-LgBpDSYQ''2013-05-16 14:57:05'10),
    (
    181'Video of .....'NULL'fZLIYR_0-CQ''2013-05-16 16:14:50'10),
    (
    191'Video of ......'NULL'8CtEksBPia0''2013-05-16 16:17:29'10); 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    isn't that the weirdest? i also get 4 of the 6 rows returned

    but there's good news, everybody

    change uid=0 to uid='0' and it works exactly as expected

    my guess is, when you say uid=0, it converts uid to an integer!

    and converting a string to an integer, it proceeds left to right, and abandons the conversion at the first non-numeric character

    thus ...
    Code:
    SELECT id
         , uid
         , CAST(uid AS UNSIGNED) as n
      FROM media
    
    id  uid          n
    19  8CtEksBPia0  8
    13  8K-LgBpDSYQ  8
    18  fZLIYR_0-CQ  0
    11  LdeDXCKphpE  0
    12  Mqkh6EgbBZc  0
    10  PCHGsCvwgvg  0
    notice the leftmost character of uid, and the integer conversion

  5. #5
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think of a thing like that but it VERY VERY STRANGE ^^
    Thanks for the explanation.


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
  •