Asator Posted December 11, 2010 Share Posted December 11, 2010 I'm attempting to implement a simple social networking system but at the moment am confused about how to create a multiple query which will display a certain user's friends list. The database contains four tables, the two tables that I'm using at the moment at 'usersTable' and 'friendshipsTable' are detailed below. usersTable | Table that stores all the user data UserID | Default primary key Forename | Surname | Username | Password | Email Address | friendshipTable | Table that stores information about friendships between users FriendshipID | Default primary key userID_1 | UserID userID_2 | UserID Status | Either Pending or Confirmed. The user's id is parsed into the url, and then saved into a variable. blah.com/userprofile.php?id=6 $id = $_GET['id']; I am familiar with creating simple queries, but can't quite work out how to set up multiple table queries. What the query needs to do is to check the userID that is parsed with the url, and then check the friendshipsTable by checking if either the userID_1 or userID_2 field matches the userID to grab the records from the table where there is a match. The next step is to check to see if the friendship is 'Confirmed' or 'Pending' and if it is 'Pending' to ignore it. Once the records have then been chosen I need the query to then check the value in either userID_1 or userID_2 that doesn't match userID and then pull the user's username and name from the usersTable so it can be displayed on a webpage. I've no idea hoe much I may or may not be overcomplicating this, an example of the code that I've got so far for this query can be found below, but that's as far as I've got at the moment. $displayFriends = mysql_query("SELECT * FROM friendshipTable, usersTable WHERE friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' "); Cheers for any help. Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/ Share on other sites More sharing options...
OOP Posted December 11, 2010 Share Posted December 11, 2010 You can use one of the MYSQL join statements. Please refer to the below link to find more information http://dev.mysql.com/doc/refman/5.0/en/join.html Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1145715 Share on other sites More sharing options...
Asator Posted December 11, 2010 Author Share Posted December 11, 2010 Cheers, with that nudge in the right direction I've now got it display friendships with this line of code: $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' "); At the moment however it display's both users in the friendship, whilst I only need it to display the user's that the selected user is friends with. Is it possible to correct this in the query, or would it simply be easier to put the output in an if statement after the query has been fetched? Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1145726 Share on other sites More sharing options...
OOP Posted December 11, 2010 Share Posted December 11, 2010 Okay... can you please explain the two columns userID_1, userID_2? Which one has the value of the selected userid in your url? Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1145867 Share on other sites More sharing options...
Asator Posted December 11, 2010 Author Share Posted December 11, 2010 userID_1 and userID_2 store the userIDs of the two friends in the friendship. When a friendship is created, the user that requested the friendship is saved in userID_1 and the other user in userID_2. The current query searches for the userID in both of these as it is looking for any friendship in the table that the user is part of. So, as an example. The selected user has the userID of '6'. The query searches the relationshipTable for the value 6 in both the userID_1 and userID_2 fields to find that user. Example Table friendshipID userID_1 userID_2 status 1 2 6 Confirmed 2 2 4 Pending 3 5 6 Confirmed Currently the query will return the details for users 2, 5 and 6 whilst I want it to only return the details for users 2 and 5. Hope that explained it a little better? Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1145910 Share on other sites More sharing options...
laffin Posted December 11, 2010 Share Posted December 11, 2010 $fetchFriends = mysql_query("SELECT DISTINCT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' "); try that Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1145915 Share on other sites More sharing options...
OOP Posted December 12, 2010 Share Posted December 12, 2010 Hi there, You can change your select statement as shown below in order to exclude the selected user $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE usersTable.UserID != '$id' AND friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' "); Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1146095 Share on other sites More sharing options...
Asator Posted December 12, 2010 Author Share Posted December 12, 2010 Thanks for the reply, that's closer to the solution, but something still doesn't seem to be working. For some reason if the user's ID is stored in the userID_1 field then it display's correctly, but if the ID is stored in userID_2 field then both users in the friendship are displayed. friendshipID userID_1 userID_2 status 1 3 6 Confirmed 2 6 4 Confirmed 3 2 6 Confirmed 4 6 5 Confirmed So the user with userID = 3 only displays user 6 on their friendslist. Where as user 6 displays users; 3, 4, 5, 6, & 6 on their friendlist. This is with the current query as: $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE usersTable.UserID != '$id' AND friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' "); Switching the friendshipTable.userID's around to make the query read as: $fetchFriends = mysql_query ..... AND friendshipTable.userID_2='$id' OR friendshipTable.userID_1='$id' "); Then lists user 3's friendlist as 3 and 6. So it seems the problem seems to be with which field the userID is stored in and which userID is checked first. I have little experience with queries this complex, so is the query simply too long or what? Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1146126 Share on other sites More sharing options...
OOP Posted December 12, 2010 Share Posted December 12, 2010 Just change your select statement to this $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE usersTable.UserID != '$id' "); Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1146135 Share on other sites More sharing options...
Asator Posted December 12, 2010 Author Share Posted December 12, 2010 Thanks again for the reply, however that doesn't seem to do the trick as when testing it for a member that doesn't have any friends, their friends list appears as all the other members in the database. I've tried looking at this from a different angle now, with subqueries and so far have got this: $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE usersTable.UserID != '$id' ") UNION ALL ("SELECT * FROM usersTable INNER JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id'")); Which still doesn't work, but hopefully might be closer to the solution? Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1146156 Share on other sites More sharing options...
Asator Posted December 12, 2010 Author Share Posted December 12, 2010 Eurghh thats embarassing, looks like I've fixed it by just throwing in some brackets. $fetchFriends = mysql_query("SELECT * FROM usersTable JOIN friendshipTable ON friendshipTable.userID_1=usersTable.userID OR friendshipTable.userID_2=usersTable.userID WHERE (friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id') AND usersTable.UserID!= '$id'"); By putting friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' In brackets seems to have gotten it behaving properly. Thanks for helping Quote Link to comment https://forums.phpfreaks.com/topic/221313-simple-social-network-system-mysql-query-help-needed/#findComment-1146168 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.