[SOLVED] Query needs tweak

[thefollower]

I have a simple query but i need to make it more complex to get an accurate returned value.

 

This is what i got and I'll explain what I am trying to do:

$CountFriends = mysql_query("SELECT * FROM friendslist WHERE UserID='$UserID'")
or die(mysql_error());
$TotalFriends = mysql_num_rows($CountFriends);

 

Now this currently counts all users that the person has added to their friends lists. Now i need to also add to that the number users who have added the current user to their lists too. But i do not want it do the mistake of if both say User1 and User2 have each other on their lists that it will count that as 2. It should count it was 1.

 

Just to explain encase your confused.. Lets say user1 added user2 now user2 doesn't necessarily have user1 on their list. This means User1 total = 0 and User2 total = 1.

 

Now if User1 and user2 had each other it would then be User1= 1 and User2 = 1 total.

 

Is it possible to not double count by accident?

[GingerRobot]

Presumably you have another field which contains the ID of the person they added? A simple or clause would do:

 

$CountFriends = mysql_query("SELECT * FROM friendslist WHERE UserID='$UserID' OR otherfield='$UserID'")
or die(mysql_error());
$TotalFriends = mysql_num_rows($CountFriends);

[rab]

SELECT COUNT(field) as friends FROM friendslist WHERE UserID='$UserID' OR otherfield='$UserID'

 

MySQL COUNT() can help you.

[thefollower]

Presumably you have another field which contains the ID of the person they added? A simple or clause would do:

 

$CountFriends = mysql_query("SELECT * FROM friendslist WHERE UserID='$UserID' OR otherfield='$UserID'")
or die(mysql_error());
$TotalFriends = mysql_num_rows($CountFriends);

 

Don't think thats quite correct.

 

Basically i got this in my table:

 

UserID | FriendID

 

 

 

UserID is the owner of the list and the FriendID that the user has added to this list.

 

So now i need a way to test if FriendID has $_SESSION['Current_User'] but it should only need to check that if UserID does "not" have FriendID because it does not need to check both ways as it will only count it as 1 anyway..

[thefollower]

Perhaps this can help :

 

This first one as you checks if how many friends the user has on "their" list

 

$CountFriends = mysql_query("SELECT * FROM friendslist WHERE UserID='$UserID'")
or die(mysql_error());
$TotalFriends = mysql_num_rows($CountFriends);

 

Now this next one checks how many people have that user on their list

 

$Count = mysql_query("SELECT * FROM friendslist WHERE FriendID='$UserID'")
or die(mysql_error());
$Total= mysql_num_rows($Count);

 

 

Now if i added the two mysql_num_rows together... effectively there may be some duplicates if both are true.. which means total friends would be 2 when really it should be 1.. so i need a way to know when its actually a duplicate and when it is .. only count it as 1 rather 2... does that make more sense.. ? I hope it did.. my first post wasn't very good explanation I don't think.

[marcus]

You can use an IN statement.

 

$count = mysql_query("SELECT count(*) AS total_friends FROM `friendslist` WHERE `FriendID` IN('$your_id','$other_id')") or die(mysql_error());
$row = mysql_fetch_assoc($count);
echo $row['total_friends'];

[thefollower]

You can use an IN statement.

 

$count = mysql_query("SELECT count(*) AS total_friends FROM `friendslist` WHERE `FriendID` IN('$your_id','$other_id')") or die(mysql_error());
$row = mysql_fetch_assoc($count);
echo $row['total_friends'];

 

I put that and got:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\xampp\htdocs\viewprofile.php on line 430

 

Query:

$countfriends = mysql_query("SELECT count(*) AS total_friends FROM `friendslist` WHERE `FriendID` IN({'$_SESSION['Current_User']}','$UserID')") or die(mysql_error());
$rowfriends = mysql_fetch_assoc($countfriends);

[marcus]

$countfriends = mysql_query("SELECT count(*) AS total_friends FROM `friendslist` WHERE `FriendID` IN('{$_SESSION['Current_User']}','$UserID')") or die(mysql_error());
$rowfriends = mysql_fetch_assoc($countfriends);