Replace "0" with "Anonymous"

[DoubleDee]

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

Here is the query I created...

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?????

[SpacePhoenix]

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.

[r937]

yeah, you got the CASE syntax wrong, and also INNER JOIN is wrong

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

[DoubleDee]

yeah, you got the CASE syntax wrong, and also INNER JOIN is wrong

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...

	$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

[r937]

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

[DoubleDee]

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

[Jeff Mott]

This may be a minority opinion, but I think this is something best done at the templating level.

<?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').

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

[Jeff Mott]

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

[r937]

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

[DoubleDee]

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

[r937]

here's a third suggestion for your visitor list --

show only the logged-in visitors, ignore the anonymous ones

[DoubleDee]

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

[itmitică]

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.

[DoubleDee]

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

[r937]

if you're gonna display anonymous visits separately, perhaps you should subtract the bots

[DoubleDee]

if you're gonna display anonymous visits separately, perhaps you should subtract the bots

I don't understand what you mean?


Debbie

[itmitică]

Visitors can be human or bots. Though you can also have member bots: http://www.sitepoint.com/forums/show...r-of-the-Month

[DoubleDee]

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...

if you're gonna display anonymous visits separately, perhaps you should subtract the bots

Debbie

[itmitică]

By IP range and UA strings. Known, well behaved spiders come from specific IP ranges: http://support.google.com/webmasters...n&answer=80553