Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Logging Non-Members

    Ugh!!!!!!

    Okay, let me try and explain this in under one million words?!

    I have a PHP script which logs whenever someone visits a Member's Profile.

    The visitor_log table looks like this...
    Code:
    id (UK) AutoIncrement
    member_viewed_id (PK1)(FK)
    visitor_id (PK2)(FK)
    created_on (PK3)

    And my member table looks like this...
    Code:
    id (PK) AutoIncrement, MediumInt, Unsigned

    Here is the problem...

    If a Non-Member (i.e. No MemberID) visits a Member's Profile, I still want/need something to stick in "visitor_id".

    Originally I was going to stick a NULL in there, but then I realized that won't work since it part of my Composite Primary Key.

    So then I decided to just insert a "0" in there.

    Problem is that then I would have to create a "dummy" Member profile for Anonymous Visitors. (Think "Referential Integrity"!!)

    Also, since the field is MEDIUMINT UNSIGNED, I can't use "0"?!

    And I don't want to change the Data-Type for several reasons.


    I *really* wanted to insert a NULL or "0" in there to make Anonymous Visitors stand out, but I don't see an easy way to do this...

    Should I break down, create a "dummy" Member record with a "1" as the MemberID and use that whenever an Anonymous Visitor needs to be logged?

    There must be a better way to go about all of this...

    Sincerely,


    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Why can't you use 0 with MEDIUMINT UNSIGNED???

    Oh, I see...because you can't have a PRIMARY KEY with value 0 in that case.

    I had a similar problem, but my primary key field was signed, not unsigned, so I just used -1 as my "catch all" and that you can do. I don't know why MySQL treats zero as "poison".

    Hate to say it, but under the circumstances I'd go with the "1 means not a member" hack.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    FWIW I think it's a pretty silly restriction that MySQL imposes. It's truly idiotic that, for the purpose of auto_increment fields (and in some other places) they treat zero and null as being equivalent. I think they must be too used to PHP sloppiness. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Why can't you use 0 with MEDIUMINT UNSIGNED???

    Oh, I see...because you can't have a PRIMARY KEY with value 0 in that case.

    I had a similar problem, but my primary key field was signed, not unsigned, so I just used -1 as my "catch all" and that you can do. I don't know why MySQL treats zero as "poison".

    Hate to say it, but under the circumstances I'd go with the "1 means not a member" hack.
    I did some checking around, and apparently while if you try and INSERT a NULL or 0 MySQL will use that as a cue to use the next number in the AutoIncrement sequence, you *can* run and UPDATE and change the Int AutoIncrement from <whatever> to "0".

    So I guess that gives me what I want, although I must confess being able to INSERT a NULL and not have a dummy Member record of "0" would be best.

    Sincerely,


    Debbie

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    > you *can* run and UPDATE and change the Int AutoIncrement from <whatever> to "0".

    A hack, but not a horrible one.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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