bytesize Posted March 5, 2011 Share Posted March 5, 2011 Table friends users Fred Tom Julie Henry Bill Wally Joe Joe friendwith Joe Joe Joe Joe Joe Joe Julie Wally Fred, Tom, Julie, Henry, Bill, and Wally have friended Joe. <?php $username = 'Joe'; $query = "SELECT * FROM friends WHERE friendwith=' $username'"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo $row['users']."<br/>"; echo $row['friendwith']."<br/>"; } ?> Joe is friends with Julie and Wally. <?php $username = 'Joe'; $query = "SELECT * FROM friends WHERE users='$username'"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo $row['users']."<br/>"; echo $row['friendwith']."<br/>"; } ?> Here's where I need your expert advice! I want to know who friended Joe except for those that Joe has friended. In other words, I want to show Fred, Tom, Henry, and Bill. Julie and Wally should not be included in the query because Joe has friended them as well. Quote Link to comment Share on other sites More sharing options...
cunoodle2 Posted March 5, 2011 Share Posted March 5, 2011 You will need to use a sub query and/or a "WHERE NO IN" etc... Try putting it together and see what you come up with and we will help you perfect it.. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 5, 2011 Author Share Posted March 5, 2011 I will try your method of sub query and post back later. Thanks for your reply. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 5, 2011 Share Posted March 5, 2011 You already have a thread open for this. Do not double post. Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 7, 2011 Author Share Posted March 7, 2011 Table users code Fred Julie Bill Wally Joe Table friends f_userid Fred Julie Bill Wally Joe Joe friendwith Joe Joe Joe Joe Julie Wally Joe is friends with Julie and Wally. <?php $username = 'Joe'; $myfriends = "SELECT * FROM users JOIN friends ON users.code = friends.friendwith WHERE friends.f_userid ='$username'"; $myfriendsresult = mysql_query($myfriends); while($myfriendsrow = mysql_fetch_array($myfriendsresult)) { echo $myfriendsrow['f_userid']."<br/>"; echo $myfriendsrow['friendwith']."<br/>"; } ?> Fred, Julie, Bill, and Wally have friended Joe. <?php $username = 'Joe'; $query = "SELECT * FROM users JOIN friends ON users.code = friends.f_userid WHERE friends.friendwith='$username'"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo $row['f_userid']."<br/>"; echo $row['friendwith']."<br/>"; } ?> Julie and Wally should not be included in the query because Joe has friended them as well. I want to know who friended Joe except for those that Joe has friended. In other words, I want to show Fred and Bill. Julie and Wally should not be included in the query because Joe has friended them as well. ============================================ I've included my attempt at creating a subcategory. This subcategory shows Fred and Bill plus Joe the User because Joe is friends with Julie and Wally. I need to show only Fred and Bill. <?php $query = "SELECT DISTINCT * FROM users JOIN friends ON users.code = friends.f_userid WHERE NOT EXISTS (SELECT * FROM friends WHERE users.code = friends.friendwith AND friends.f_userid='$username')"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo $row['f_userid']."<br/>"; echo $row['friendwith']."<br/>"; } ?> Pointing me in the right direction will be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 7, 2011 Share Posted March 7, 2011 Try this mate. Made a database just for it and it worked SELECT users, friendwith FROM friends WHERE users <> 'Joe' AND users NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 7, 2011 Author Share Posted March 7, 2011 users is a table and code is a column in users. SELECT users, friendwith FROM friends WHERE users <> 'Joe' AND users NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') I tried this but it doesn't work. SELECT code, friendwith FROM friends, users WHERE code <> 'Joe' AND code NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 7, 2011 Share Posted March 7, 2011 What do you need code for? Isn't just the user name enough? Can't you use the original table? Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 7, 2011 Share Posted March 7, 2011 Then replace code with f_userid Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 7, 2011 Author Share Posted March 7, 2011 Yes, I only need table friends, but table users is needed to display the images of those that are not friends with f_userid. I can add table users after solving friends table. Still not working. SELECT f_userid, friendwith FROM friends WHERE f_userid <> 'Joe' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 7, 2011 Author Share Posted March 7, 2011 f_userid and friendwith columns are both equal to Joe in different rows depending on who is friends with who. Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 7, 2011 Share Posted March 7, 2011 SELECT f_userid, friendwith FROM friends WHERE f_userid <> 'Joe' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') This must work otherwise you're doing something wrong. What you're getting? Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 7, 2011 Author Share Posted March 7, 2011 I copied your query and nothing happens because f_userid and friendwith both contain Joe. Doesn't <> represent not equal to? SELECT f_userid, friendwith FROM friends WHERE f_userid <> 'Joe' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') When I echo the query, I get the query with Joe. Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 7, 2011 Share Posted March 7, 2011 This is what I get in PHPMyAdmin, you should test it too (maybe your query's right but something with the PHP code is bugging you): Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 8, 2011 Author Share Posted March 8, 2011 Table friends f_userid Fred Tom Henry Bill Julie Wally Joe Joe friendwith Joe Joe Joe Joe Joe Joe Julie Wally This what your table should look like. Joe is in both columns because Joe friended Julie and Wally but not the others. I get the same query you get when it echos out on my page. This is why I'm having trouble with the query. Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 8, 2011 Share Posted March 8, 2011 Do you want to show my table with 4 names or your table? Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 8, 2011 Author Share Posted March 8, 2011 You can use this table example. I'm trying to isolate Fred and Tom because Joe has not made friends with them. f_userid Fred Tom Henry Bill Joe Joe friendwith Joe Joe Joe Joe Henry Bill Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 8, 2011 Share Posted March 8, 2011 Changed my table to yours got still same result, man. Fred and Tom =/ So you must be doing something wrong. Could I get a link to your site so I can see what's going on? Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 8, 2011 Author Share Posted March 8, 2011 This is my php. <?php $username = 'Joe'; $query = "SELECT f_userid, friendwith FROM friends WHERE f_userid <> '$username' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> '$username')"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo $row['f_userid']."<br/>"; echo $row['friendwith']."<br/>"; } ?> This is what I get when I echo the $query. SELECT f_userid, friendwith FROM friends WHERE f_userid <> 'Joe' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') Nothing echos out for $row['f_userid'] or $row['friendwith']. If I change the query to: SELECT f_userid, friendwith FROM friends WHERE f_userid = 'Joe' AND f_userid NOT IN (SELECT friendwith FROM friends WHERE friendwith <> 'Joe') $row['f_userid'] and $row['friendwith'] echo out content form table friends using the query with =. Quote Link to comment Share on other sites More sharing options...
sasa Posted March 8, 2011 Share Posted March 8, 2011 try <?php $username = 'Joe'; $myfriends = "SELECT * FROM users JOIN friends ON users.code = friends.f_userid WHERE friends.friendwith ='$username' AND friends.f_userid NOT IN (SELECT friendwith FROM friends WHERE f_userid='$username')"; $myfriendsresult = mysql_query($myfriends); while($myfriendsrow = mysql_fetch_array($myfriendsresult)) { echo $myfriendsrow['f_userid']."<br/>"; echo $myfriendsrow['friendwith']."<br/>"; } ?> not teted Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 8, 2011 Author Share Posted March 8, 2011 sasa, That did it, it works great. @ silkfire, I recreated your table and query and it worked for me, but would not work with the other code. Thank you both very much, I really appreciate your help. Quote Link to comment Share on other sites More sharing options...
silkfire Posted March 9, 2011 Share Posted March 9, 2011 Niced it finally worked! What "other code" are you speaking about? Quote Link to comment Share on other sites More sharing options...
bytesize Posted March 9, 2011 Author Share Posted March 9, 2011 The users table seems to be the missing link. Quote Link to comment Share on other sites More sharing options...
sasa Posted March 10, 2011 Share Posted March 10, 2011 the solutin is given in 1st replay ( cunoodle2 ) but discusion goes to wrong way Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.