SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 44 of 44
  1. #26
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm all for changing the values of the duplicates and then alerting those users that the value was changed. Or, at least if I can find out if I have any dupes. I just don't want to lose anything without knowing about it

    The query returned
    usr_token = empty
    dupes = 1701

    I believe the dupes here would be all of the rows that have empty usr_token fields.

    I ran the same query with the usr_key field and had the same results.

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    my advice is to change all the empty strings to NULLs

    that way, you will be able to have a UNIQUE index, which will still allow hundreds of NULLs while disallowing duplication of any non-NULL value

    of course, this means you have to change the table, because right now those columns are specified as NOT NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I copied 'users' to a new table. In the new table I then changed the usr_token field to NULL. I then ran
    Code:
    UPDATE users set usr_token=NULL WHERE usr_token=''
    I then edited the field by making it UNIQUE and did NOT receive an error. I did the same for usr_key and did not receive an error.

    If this is what you were intending, I'll go ahead and do it to my production table.

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sounds good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the future, should I always create UNIQUE fields as NULL?

    Also, what about GUID, or UUID. Would this prevent me from encountering this kind of issue again?

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tgavin View Post
    In the future, should I always create UNIQUE fields as NULL?
    no, not if NOT NULL is appropriate

    Quote Originally Posted by tgavin View Post
    Also, what about GUID, or UUID. Would this prevent me from encountering this kind of issue again?
    sorry, i don't understand what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doesn't matter. I'll research it on my own. I've taken up enough of your time

    Thank you very much, and Happy New Year!

  8. #33
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ugh. Now, when performing an INSERT I'm intermittently getting

    Duplicate entry 'NULL' for key 4

    I manually put NULL in the INSERT query if the field was to be empty, otherwise I'd get an error. So, it seems like using NULL fields is creating an error no matter what.

    If the value for usr_token or usr_key is empty, then the INSERT is rejected.

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    without seeing your SQL, i can only guess...

    but i'd say you were inserting the 4-character string 'NULL' instead of the keyword NULL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #35
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's exactly what I was doing! Fixed!

    Thanks!

    BTW: you mentioned previously that I should use NOT NULL if appropriate. How do I know when it's appropriate?

  11. #36
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    NULL is appropriate when the column value for a paricular row in unknown, none, not applicable, or missing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #37
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks

  13. #38
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. I looked into UUID and didn't find as much as I was hoping for, at least in explanations that made sense to me.

    My fields, usr_token and usr_key both have to be unique for each user. Currently, I'm using PHP to generate these: uniqid(rand(), false). The problem that I'm worried about is duplicates. That's what started this thread. So, with your help I was able to change those fields to UNIQUE.

    Now, the next part of the problem is that when somebody registers on the site and a token is created it needs to be unique. Since I'm creating this in PHP, I would have to do a lookup first to see it it's already in use. If it is, I haven't been able to figure out a way to automatically generate a new one and then lookup again. It's kind of a nightmare scenario waiting to happen.

    So, I'm wondering about using UUID() to create the token and key

    Code MySQL:
    INSERT INTO users (
        usr_fname,
        usr_lname,
        usr_token,
        usr_key
    ) VALUES (
        'Giovanni',
        'Jones',
        UUID(),
        UUID()
    )

    Would that work? Would that prevent duplicate entries for those fields?

  14. #39
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    pre-SELECT is not needed

    php pseudo-code:

    while no success {
    INSERT using random uuid /* php generated is fine */
    if no error returned, success
    }

    let the database to the select for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #40
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good idea

    Back to uuid. Should I use that in the query? I've noticed that a couple of rows in the users table don't have the required amount of characters and some even have decimals. Obviously, the php function I'm using isn't cutting it. Probably because I'm doing something wrong...

  16. #41
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would like to help you, but i don't do php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #42
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't dare asking!

    I'm just curious if I should do it in mysql, as in my earlier post, like so
    Code MySQL:
    INSERT INTO users (usr_token) VALUES (UUID())
    would that let me forget about using the PHP function and only use sql?

  18. #43
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, but you would still want to use my pseudo-code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #44
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    absolutely!

    Thanks again!


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
  •