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

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.

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

---

Quote:

---

Originally Posted by r937

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

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

Quote:

---

Originally Posted by 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

---

Fair enough.

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

Quote:

---

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

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

---

Quote:

---

Originally Posted by DoubleDee

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

Quote:

---

Originally Posted by DoubleDee

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

Quote:

---

Originally Posted by r937

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

:lol:


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

Quote:

---

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

Quote:

---

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

Quote:

---

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

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

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

Quote:

---

Originally Posted by r937

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

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

---

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


Debbie

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.

Quote:

---

Originally Posted by 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.

---

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

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

Quote:

---

Originally Posted by r937

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

---

I don't understand what you mean?


Debbie

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

Quote:

---

Originally Posted by itmitică

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

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