doubledee — 2012-06-27T00:08:41-04:00 — #1
I am working on a new feature which shows the "Last 10 Visitors" to a Member's Profile.
Here is the query I created...
FROM member AS m
INNER JOIN visitor_log AS v
In my "visitor_log" table in the "visitor_id" column I have these values...
...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?
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 — 2012-06-27T02:18:22-04:00 — #2
v.visitor_id = 0 THEN 'Anonymous Visitor'
END AS username
member AS m
visitor_log AS v
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 — 2012-06-27T04:29:32-04:00 — #3
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
JOIN member AS m
ON m.id = v.visitor_id
WHERE v.member_viewed = 19
doubledee — 2012-06-27T17:24:25-04:00 — #4
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
ORDER BY v.created_on DESC
r937 — 2012-06-27T17:57:23-04:00 — #5
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 — 2012-06-27T18:25:44-04:00 — #6
BTW, I just noticed that if username1 visits Debbie's Profile 4 times, then I get...
The last 10 visitors on this page were...
I only want "username1" to appear once, since there is a difference between a "visit" and a "visitor".
jeff_mott — 2012-06-27T18:33:52-04:00 — #7
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: ?>
<?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').
FROM visitor_log vl
LEFT JOIN member m
ON vl.member_viewed = m.id
WHERE vl.member_viewed = ?
jeff_mott — 2012-06-27T19:04:26-04:00 — #8
"DISTINCT" is what you're looking for.
SELECT DISTINCT m.username
r937 — 2012-06-27T19:15:25-04:00 — #9
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 — 2012-06-27T19:34:53-04:00 — #10
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...
At the same time, as you have correctly pointed out, if I have 9 unique Anonymous Visitors, then I would want to see...
How can I have it both ways?
(Maybe that is too much for one SQL statement?!
Would it look weird, or be bad - for whatever reason - to display the Visitors IP Address if they are "Anonymous"?! :-/
r937 — 2012-06-27T19:39:42-04:00 — #11
here's a third suggestion for your visitor list --
show only the logged-in visitors, ignore the anonymous ones
doubledee — 2012-06-27T19:56:31-04:00 — #12
That same thought occurred to me as well.
Although, I think the example I gave above with Anonymous (22.214.171.124) seems pretty cool...
system — 2012-06-30T12:10:32-04:00 — #13
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 — 2012-06-30T12:30:05-04:00 — #14
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.
r937 — 2012-06-30T13:26:25-04:00 — #15
if you're gonna display anonymous visits separately, perhaps you should subtract the bots
doubledee — 2012-06-30T13:31:38-04:00 — #16
I don't understand what you mean?
system — 2012-06-30T13:51:08-04:00 — #17
doubledee — 2012-06-30T13:53:12-04:00 — #18
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...
system — 2012-06-30T13:59:04-04:00 — #19
By IP range and UA strings. Known, well behaved spiders come from specific IP ranges: http://support.google.com/webmasters/bin/answer.py?hl=en&answer=80553
mittineague — 2014-09-22T11:53:32-04:00 — #20
This topic is now archived. It is frozen and cannot be changed in any way.