SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Replace "0" with "Anonymous"

    I am working on a new feature which shows the "Last 10 Visitors" to a Member's Profile.

    Here is the query I created...

    Code MySQL:
    SELECT m.username
    FROM member AS m
    INNER JOIN visitor_log AS v
    ON m.id=v.visitor_id
    WHERE v.member_viewed=19


    In my "visitor_log" table in the "visitor_id" column I have these values...

    0
    20
    21
    25

    ...which represent different Usernames.

    The "0" is what my PHP code inserted into the table when an anonymous or un-logged-in person visits the member's Profile.

    I need the "0" returned in my record-set, AND I want a label like "Anonymous Visitor" to appear for m.username

    I was considering just creating a record in my "member" table with an "id=0" and "username=Anonymous Visitor" but that doesn't seem proper.

    So is there a way to swap any 0's for a label like "Anonymous Visitor" in the query above?

    Thanks,


    Debbie

    P.S. Should I be using the MySQL tag to show SQL in this thread, or is there an SQL tag which is better?????

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,016
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT
        CASE
            m.username
        WHEN
            v.visitor_id = 0 THEN 'Anonymous Visitor'
        ELSE
            m.username
        END AS username
    FROM
        member AS m
    INNER JOIN
        visitor_log AS v
            ON m.id=v.visitor_id
    WHERE
        v.member_viewed=19

    I think the syntax is right, it's been a while since I've used CASE so I may have got the syntax wrong.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, you got the CASE syntax wrong, and also INNER JOIN is wrong
    Code:
    SELECT COALESCE(m.username,'Anonymous Visitor') AS username
      FROM visitor_log AS v
    LEFT OUTER
      JOIN member AS m
        ON m.id = v.visitor_id
     WHERE v.member_viewed = 19
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, you got the CASE syntax wrong, and also INNER JOIN is wrong
    Code:
    SELECT COALESCE(m.username,'Anonymous Visitor') AS username
      FROM visitor_log AS v
    LEFT OUTER
      JOIN member AS m
        ON m.id = v.visitor_id
     WHERE v.member_viewed = 19
    How about this...

    Code SQL:
    	$q8 = "SELECT IF(v.visitor_id=0,'Anonymous', m.username) AS username
    			FROM visitor_log AS v
    			LEFT JOIN member AS m
    			ON m.id = v.visitor_id
    			WHERE v.member_viewed=?
    			ORDER BY v.created_on DESC
    			LIMIT 10";


    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that'll work too, except that i prefer always to use a standard sql construct, rather than a proprietary function that works only in one database system
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that'll work too, except that i prefer always to use a standard sql construct, rather than a proprietary function that works only in one database system
    Fair enough.

    BTW, I just noticed that if username1 visits Debbie's Profile 4 times, then I get...

    The last 10 visitors on this page were...

    username1
    username1
    username1
    username1
    Steve Miller
    Robert Redford
    Bette Davis
    Anonymous Visitor

    I only want "username1" to appear once, since there is a difference between a "visit" and a "visitor".


    Debbie

  7. #7
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    This may be a minority opinion, but I think this is something best done at the templating level.

    Code PHP:
    <?php if ($visitorUsername) ?>
        <?php echo htmlspecialchars($visitorUsername) ?>
    <?php else: ?>
        Anonymous
    <?php endif ?>

    To do this, you'd have to change your SQL query from an inner join to a left join, because you want to retrieve visitor records even if there's no corresponding member (as is the case for '0').

    Code SQL:
    SELECT m.username
    FROM visitor_log vl
    LEFT JOIN member m
        ON vl.member_viewed = m.id
    WHERE vl.member_viewed = ?

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I only want "username1" to appear once, since there is a difference between a "visit" and a "visitor".
    "DISTINCT" is what you're looking for.

    SELECT DISTINCT m.username

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I only want "username1" to appear once, since there is a difference between a "visit" and a "visitor".
    and there is also a difference between one anonymous visitor and another

    and DISTINCT will lump them all together, skewing the impression of actual visit patterns, because now there will always be 9 real visitors and only one anonymous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and there is also a difference between one anonymous visitor and another

    and DISTINCT will lump them all together, skewing the impression of actual visit patterns, because now there will always be 9 real visitors and only one anonymous
    So what should I do?

    I guess I sorta want it both ways?!




    If r937, as a registered and logged in member, visits DoubleDee's Profile 9 times - because deep down we know he still loves me - then I do NOT want him filling up my "Last 10 Visitors" like this...

    r937
    r937
    r937
    r937
    r937
    r937
    r937
    r937
    r937
    Jeff Mott

    At the same time, as you have correctly pointed out, if I have 9 *unique* Anonymous Visitors, then I would want to see...

    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Anonymous
    Jeff Mott
    How can I have it both ways?

    (Maybe that is too much for one SQL statement?!


    Off Topic:


    Would it look weird, or be bad - for whatever reason - to display the Visitors IP Address if they are "Anonymous"?!

    Like this...

    Anonymous (58.1.2.1)
    Anonymous (58.1.2.2)
    Anonymous (58.1.2.3)
    Anonymous (58.1.2.4)
    Anonymous (58.1.2.5)
    Anonymous (58.1.2.6)
    Anonymous (58.1.2.7)
    Anonymous (58.1.2.8)
    Anonymous (58.1.2.9)
    Jeff Mott



    Debbie

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here's a third suggestion for your visitor list --

    show only the logged-in visitors, ignore the anonymous ones
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here's a third suggestion for your visitor list --

    show only the logged-in visitors, ignore the anonymous ones
    That same thought occurred to me as well.

    Although, I think the example I gave above with Anonymous (58.1.2.1) seems pretty cool...


    Debbie

  13. #13
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    I'm with Jeff on this one. This type of computing belongs in the reporting logic.

    One thing to note as well, usually looking at member's profiles usually means one has to be logged in. It's only fair.

    But you could have a "Anonymous visits" section, to count the anonymous views. Displaying visitors IP is extremely unfriendly.

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    I'm with Jeff on this one. This type of computing belongs in the reporting logic.

    One thing to note as well, usually looking at member's profiles usually means one has to be logged in. It's only fair.

    But you could have a "Anonymous visits" section, to count the anonymous views. Displaying visitors IP is extremely unfriendly.
    1.) So if I have time, maybe I'll go back and break things out using two queries, and have a listing of Members who visited the Member's Profile, and then have another heading "___ Anonymous Visitors" so a person can get an accurate gauge of WHO and HOW MANY people are checking them out?!

    2.) I can drop the IP Addy idea.

    Thanks,


    Debbie

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you're gonna display anonymous visits separately, perhaps you should subtract the bots
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you're gonna display anonymous visits separately, perhaps you should subtract the bots
    I don't understand what you mean?


    Debbie

  17. #17
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Visitors can be human or bots. Though you can also have member bots: http://www.sitepoint.com/forums/show...r-of-the-Month

  18. #18
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    Visitors can be human or bots.
    And so how would I know whether an Anonymous Visitor was a legitimate human or a bot?! (I wouldn't.)


    So what does this mean...
    Quote Originally Posted by r937
    if you're gonna display anonymous visits separately, perhaps you should subtract the bots

    Debbie

  19. #19
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    By IP range and UA strings. Known, well behaved spiders come from specific IP ranges: http://support.google.com/webmasters...n&answer=80553


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
  •