Add IP Addy to Coalese

[doubledee]

I am using this query to display the "Last 10 Visitors" to a Member's Profile...

 

$q8 = "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=?
		ORDER BY v.created_on DESC
		LIMIT 10";

 

 

The query works just fine except for the fact that if "JoeBlow" or <unknown user> visits my Profile 9 times I get this undesired result-set...

 

Anonymous

Anonymous

Anonymous

Anonymous

Anonymous

Anonymous

Anonymous

Anonymous

Anonymous

GeorgeWashington

 

 

Initially, when I saw this issue, I was just going to change things so I got this...

Anonymous

GeorgeWashington

 

But that wouldn't be accurate, since I did have 9 unique Visitors - they just all happen to have the same assigned name which is confusing?!

 

 

Since I capture everyone's IP Address when they visit a Member's profile, I am thinking it would be more useful to have an output like this for Anonymous Visitors...

 

Anonymous (75.100.20.1)

Anonymous (75.100.20.2)

Anonymous (75.100.20.3)

Anonymous (75.100.20.4)

Anonymous (75.100.20.5)

Anonymous (75.100.20.6)

Anonymous (75.100.20.7)

Anonymous (75.100.20.8 )

Anonymous (75.100.20.9)

GeorgeWashington

 

 

So, is there some fairly easy way to insert/append the IP Addy to "Anonymous" in my COALESE statement above??

 

(I'm not sure if this would be easier to do in PHP or SQL??)

 

Thanks,

 

 

Debbie

 

 

[gizmola]

Displaying the IP addresses of visitors is a bad idea.  I personally would remove all Anonymous visitors from that list --- of what value is it?

 

 

[doubledee]

Displaying the IP addresses of visitors is a bad idea.  I personally would remove all Anonymous visitors from that list --- of what value is it?

 

Just letting you or I get an accurate representation of WHO is visiting our Profiles...

 

It just seemed like a good idea to me.

 

Maybe 20 Registered Members visited my Profile.  Okay.  But maybe 200 Non-members are checking me out.  Might be interesting to know...   

 

 

Debbie

 

[gizmola]

Perhaps, but your use case was displaying the last 10 visitors.  With anonymous users there is no real information being provided, as you pointed out.  A fair compromise might be:

 

4 Anonymous users
George Washington

 

 

[doubledee]

To be devil's advocate, why is displaying Anonymous Visitors' IP Addresses a bad idea?

 

 

Perhaps, but your use case was displaying the last 10 visitors.  With anon ymous users there is no real information being provided, as you pointed out.  A fair compromise might be:

 

4 Anonymous users
George Washington

 

To do that, I guess I would need two queries, right?

 

But it sounds like you still don't see any value in displaying how many unique anonymous visitors visited a Member's Profile, huh?

 

 

Debbie

 

[gizmola]

While it is fairly common knowledge that the server has a visitor's IP address available, by disclosing it to the public, you would be stripping a level of anonymity from some visitors.  Doing a reverse IP lookup might lead other users to be able to identify the visitor or their place of origin.  Since there is no public value to doing so, you're simply going to invite criticism, and angry users who will be asking why you are displaying their IP's to the public so they can be harvested by crackers.

 

As for needing 2 queries -- no you wouldn't.  Just the original query, and a helper function that goes through the results and builds an array with a count element for the anonymous users.