doubledee Posted June 27, 2012 Share Posted June 27, 2012 Hmmm... How to explain this?! I am trying to add a feature to my website which displays the "Last 10 Visitors" to a Member's Profile. I have a "visitor_log" table with these fields and data... id member_viewed visitor_id ip hostname created_on updated_on 1 19 0 127.0.0.1 localhost 2012-06-26 17:38:14 NULL 2 19 20 127.0.0.1 localhost 2012-06-26 17:40:07 NULL 3 19 21 127.0.0.1 localhost 2012-06-26 17:40:17 NULL 4 19 25 127.0.0.1 localhost 2012-06-26 17:40:29 NULL And here is my query... SELECT m.username FROM member AS m INNER JOIN visitor_log AS v ON m.id=v.visitor_id WHERE v.member_viewed=19 The problem is that there is no record in the Member table for "0" but I want "Anonymous Visitor" to appear in my query result-set so the Member can see any non-Members that visited as well. What is the best way to do that? 1.) I could created a "filler record" in my Member table with an id=0 but that desn't see like a good idea?! 2.) I could stick the value in with PHP, but that doesn't seem like a good approach either when I want the lable "Anonymous Visitor" to show up in the database result-set! 3.) Maybe there is some way to get my SQL to swap in "Anonymous Visitor" when it sees a "0" ?? 4.) I suppose I could also display the Anonymous Visitor's IP Address in lieu of a "Username", so I would be open to that idea too. Some help is needed on this one!! Thanks, Debbie Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2012 Share Posted June 27, 2012 an alternative SELECT IF(v.vid=0,'Anonymus',m.username) AS username FROM visitor_log AS v LEFT JOIN member AS m ON m.id=v.visitor_id WHERE v.member_viewed=19; to me your #1 is a more reasonable option in case that down the row you want to change the 'Anonymus...' text to something else... that way you will need make the change in just one place Link to comment Share on other sites More sharing options...
trq Posted June 27, 2012 Share Posted June 27, 2012 See IFNULL. http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 See IFNULL. http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull That wouldn't work, because I need "Anonymous" when the visitor_id = 0 (versus NULL). Debbie Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 an alternative SELECT IF(v.vid=0,'Anonymus',m.username) AS username FROM visitor_log AS v LEFT JOIN member AS m ON m.id=v.visitor_id WHERE v.member_viewed=19; Why did you switch to a LEFT JOIN?? to me your #1 is a more reasonable option in case that down the row you want to change the 'Anonymus...' text to something else... that way you will need make the change in just one place Because if I did it the first way you showed, then if I changed what "0" represents, I would need to re-run the query for all people for the change to show, right? Debbie Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2012 Share Posted June 27, 2012 .... Why did you switch to a LEFT JOIN?? Test both version yourself and you should be able to realize why I did that. .... to me your #1 is a more reasonable option in case that down the row you want to change the 'Anonymus...' text to something else... that way you will need make the change in just one place Because if I did it the first way you showed, then if I changed what "0" represents, I would need to re-run the query for all people for the change to show, right? Debbie the alternative offered and your option#1 should not be used at the same time obviously... if you implement the option#1 you only need to eliminate the IF from the offered SQL,,,, and by the way the alternative offered by Thorpe using IFNULL also will work with the LEFT JOIN.... is just a matter to which column you choose to apply the IFNULL.... test the query without the IF that I wrote and you will see why. Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 MySQL complained when I tried to INSERT a record with an id=0 and instead gave it a "37" I did an edit and it took the "0" on the second attempt. It just feels hokey having a record with "0", "Anonymous" and a bunch of NULLs in this pseudo record... Personally, I like your query idea better... Debbie Link to comment Share on other sites More sharing options...
Jessica Posted June 27, 2012 Share Posted June 27, 2012 id member_viewed visitor_id The visitor_id should be 0, not the id. Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 id member_viewed visitor_id The visitor_id should be 0, not the id. No, in the Member (lookup) table you would need id=0 so when you have a visitor_id=0 it maps to that Member record. See? But like I said, I liked mikosiko's query solution the best. Debbie Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 Where did the "Solved" button go????? Debbie Link to comment Share on other sites More sharing options...
Jessica Posted June 27, 2012 Share Posted June 27, 2012 That's probably the worst way you could have handled it. If you keep saying you like the way mikosiko did it why didn't you do it that way? That's what I thought you were trying to do, but you're actually doing the one that no one suggested... Link to comment Share on other sites More sharing options...
doubledee Posted June 27, 2012 Author Share Posted June 27, 2012 If you keep saying you like the way mikosiko did it why didn't you do it that way? Please re-read what has been said (and done) in this thread. I said I liked this query best and I am using it... SELECT IF(v.vid=0,'Anonymus',m.username) AS username FROM visitor_log AS v LEFT JOIN member AS m ON m.id=v.visitor_id WHERE v.member_viewed=19; Debbie Link to comment Share on other sites More sharing options...
Jessica Posted June 28, 2012 Share Posted June 28, 2012 MySQL complained when I tried to INSERT a record with an id=0 and instead gave it a "37" I did an edit and it took the "0" on the second attempt. It just feels hokey having a record with "0", "Anonymous" and a bunch of NULLs in this pseudo record... Personally, I like your query idea better... Debbie THIS is what I was responding to. It's not my fault you said you did something. Forget it, good luck. Link to comment Share on other sites More sharing options...
ManiacDan Posted June 28, 2012 Share Posted June 28, 2012 Since Debbie appears to have found an answer (whatever that answer may be), it's safe to close this thread before it escalates. Link to comment Share on other sites More sharing options...
Recommended Posts