Simple Social Network System: MySQL Query help needed

[Asator]

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.

 

[OOP]

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

[Asator]

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?

[OOP]

Okay... can you please explain the two columns userID_1, userID_2? Which one has the value of the selected userid in your url?

[Asator]

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? 

[laffin]

$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

[OOP]

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' ");

 

[Asator]

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?

 

 

[OOP]

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'  ");

[Asator]

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?

 

[Asator]

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