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
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
$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";
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
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 = ?
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
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=itmitică;5144140]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]
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?!
