WHILE Help

[JohnOP]

I have a website where i will let members go to a "members" page where they see a list of members generated by my while loop, also next to there name is a add friend button which when adding them will remove them from the list becuase he has already added him (i want him to stay on the list for others who have not added him)..

 

I have a table for users which populates the members page and when i add a friend it inserts a new row into a friends table but how can i remove them from the list only for me becuase i have added them but still stay on for others who havent?

[trq]

Do you have a question?

[JohnOP]

Mmbers.php

 

Member 1 - Members 2 - Member 3

  Add              Add            Add

 

I add member 1, inserts a new row into friends,  i want him to be removed off the list for me since i have him added but stay on for everyone else who dont have him.

[seanlim]

Assuming that (1) your members table has the column members.id and (2) your friends table consists of the columns friends.member_id and friends.friend_id referencing the member's id and the friend's respectively

 

SELECT * FROM members LEFT OUTER JOIN friends ON members.id=friend_id AND friends.member_id="[Currently Logged In User's ID]"

 

Members who are not friends will have null values for the joined `friends` columns

Members who are friends will have their member ids displayed in the joined `friends` columns

 

Hopefully I make sense

[JohnOP]

I have it working

 

I'm not sure if this is the right way to do it but it seems to work.

 

$sql = mysql_query("SELECT * FROM friends WHERE username='$username'");
$row = mysql_fetch_assoc($sql);
$friend= $row['friend_username'];
$friendid= $row['friend_id'];

$mysql = mysql_query("SELECT * FROM users WHERE username != '$friend'");
while($row = mysql_fetch_assoc($mysql)){

 

 

[seanlim]

I don't think that's right. That only leaves out the first friend from the list if i'm not mistaken.

 

Edit: To rephrase, it displays the entire members list minus the first friend that it finds on the list.

[chintansshah]

I think, you should maintain flag to into the database to display it in list.

[JohnOP]

<?php
$sql = mysql_query("SELECT * FROM friends WHERE username='$username'");
$row = mysql_fetch_assoc($sql);
$friend= $row['friend_username'];

$mysql = mysql_query("SELECT * FROM users WHERE username != '$friend'");
while($row = mysql_fetch_assoc($mysql)){
?>

 

For example if i add member 1 as a friend then there username gets added to friends table so the seond query should not pick him up from the users table because of the where clause, but his name still stays in the members list, anyone have any idea why?

 

EDIT

 

Sorry seanlim i didn't see your second message, yes that it whats happening, i am trying to use your query but with no luck, i don;t know if its because i have never used inner joins lol

 

My tables are

Friends

Users

 

Users table has an id for each member

 

Friends has

id - id of the row

my_id - logged in users id

my_username - logged in users username

friend_id - id of person adding

friend_username - friend adding username

[JohnOP]

Would it be possible to add all the friends to the same collumn or is that not the best way to do it? like

 

Friends

 

When i add a user

 

Friends

User1:User2 so on so forth..

[jcbones]

With no more info than you have given, I would suggest that you use:

SELECT users.* FROM friends JOIN users ON friends.username != users.username WHERE friends.my_id = 'my_id';

[Drummin]

I have a "Buddy Table" that simply stores the "userid" and the "buddyid".  When you add someone to your buddy list it places your ID into the "userid" and the id of the buddy into the "buddyid slot.  I simply select to show all buddies by querying WHERE userid=$userid.  In my User table I also have a column where a person can block their name from showing in the buddy list selection area and if marked, also removes his records from the "Buddy Table" where buddyid=$userid.  Works great.

[JohnOP]

Gives me a mysql_fetch_assoc error.

 

I don't understand the query.

 

SELECT users.* FROM friends JOIN users ON friends.username != users.username WHERE friends.my_id = 'my_id';

 

Select everything from users FROM friends, how can that be? though i'm not sure on it

 

just to re cap what i am trying to do is

 

i have a table called users and a table called friends, a members page which i want to list all of the users

with a friend add button beside them, then when i add one of them they dont show up on the page anymore because i have them added..

 

What i am doing just now is when i click the friend add button it adds a new row to the friends table

 

id - AUTO_INCREMENT

my_id - Loggin in user id

my_username - logged in username

 

friend_id - person whom i am adding

friend_username  person whom i am adding

 

But i cant seem to pull all members from users to show on members page excluding the membersi  have already added.

[jcbones]

Gives me a mysql_fetch_assoc error.

 

I don't understand the query.

 

SELECT users.* FROM friends JOIN users ON friends.username != users.username WHERE friends.my_id = 'my_id';

 

Select everything from users FROM friends, how can that be? though i'm not sure on it

 

 

You did put the appropriate id number in 'my_id' didn't you?

 

You could also try:

SELECT * FROM users WHERE username NOT IN (SELECT friend_username FROM friends WHERE my_id = '1');

[JohnOP]

Still get mysql fetch assoc error

 

<?php $username = $_SESSION['username'];
$id = $_SESSION['id'];



$sql = mysql_query("SELECT * FROM users WHERE username NOT IN (SELECT friend_username FROM friends WHERE my_id = '$id'");


while($row = mysql_fetch_assoc($sql)){


?>
<table>
<tr><td>
<?php echo $row['username']; ?>
</td></tr>
<tr><td>
<form action="add.php?id=<?php echo $row['id']; ?>" method="POST">
<input type="submit" name="i" value="Add">
</form>
</td></tr></table>
<?php
}

[jcbones]

Still get mysql fetch assoc error

 

<?php $username = $_SESSION['username'];
$id = $_SESSION['id'];



$sql = mysql_query("SELECT * FROM users WHERE username NOT IN (SELECT friend_username FROM friends WHERE my_id = '$id'");


while($row = mysql_fetch_assoc($sql)){


?>
<table>
<tr><td>
<?php echo $row['username']; ?>
</td></tr>
<tr><td>
<form action="add.php?id=<?php echo $row['id']; ?>" method="POST">
<input type="submit" name="i" value="Add">
</form>
</td></tr></table>
<?php
}

 

You missed a closing ) in the sql statement.  This is the importance of assigning the query string to a variable, and then the variable to the query.

 

<?php $username = $_SESSION['username'];
$id = $_SESSION['id'];


$query = "SELECT * FROM users WHERE username NOT IN (SELECT friend_username FROM friends WHERE my_id = '$id')";
$sql = mysql_query($query) or trigger_error($query . ' has an error:<br />' . mysql_error());


while($row = mysql_fetch_assoc($sql)){


?>
<table>
<tr><td>
<?php echo $row['username']; ?>
</td></tr>
<tr><td>
<form action="add.php?id=<?php echo $row['id']; ?>" method="POST">
<input type="submit" name="i" value="Add">
</form>
</td></tr></table>
<?php
}