Problem with SELECT clause

cmccomas

I have a system on my website that allows members to add other members are their friends. On the member's page I have a query that I'm trying to get three random friends from the database and display them.

My table layout:

TABLE members
member_userid (pk, int)
member_fname
member_lname

TABLE friends
friend_id (pk, int)
friend1_userid
friend2_userid
friend_active

Basically, when one users goes to add another friend it inserts both user's member_userid into the friends table and sets friend_active = 0. Then when the second user accepts the friend request it sets friend_active = 1.

I'm using this query to try and get the three random friends and their name from the DB.

$friends_rs = $conn->Execute ( "SELECT * FROM members, friends WHERE friend1_userid = member_userid AND friend2_userid = member_userid ORDER BY RAND() LIMIT 3" ) or die ( $conn->ErrorMsg() );

Then I have this as the code in the HTML

<? while ( ! $friends_rs->EOF ) { ?>
	<? if ( $friends_rs->Fields("friend1_userid") == $userdata['user_id'] ) { ?>	
		<tr>
			<td align="center" class="contentItems"><img src="images/aif/<?=($friends_rs->Fields("friend2_userid"))?>.jpg" height="50%" width="50%" border="1" />
			<br /><a href="aif.php?member_userid=<?=($friends_rs->Fields("friend2_userid"))?>"><?=($friends_rs->Fields("member_fname"))?> <?=($friends_rs->Fields("member_lname"))?></a></td>
		</tr>
	<? } else { ?>
		<tr>
			<td align="center" class="contentItems"><img src="images/aif/<?=($friends_rs->Fields("friend1_userid"))?>.jpg" height="50%" width="50%" border="1" />
			<br /><a href="aif.php?member_userid=<?=($friends_rs->Fields("friend1_userid"))?>"><?=($friends_rs->Fields("member_fname"))?> <?=($friends_rs->Fields("member_lname"))?></a></td>
		</tr>							
	<? } ?>
<? $friends_rs->MoveNext(); } ?>

If I use an OR in the query it displays right right picture, but the wrong name. If I use an AND in the query it displays nothing.

What did I do wrong?

leatherback

well.. For starters..

Your database is asking for trouble..

Why do you have a colum for friend1, 2, and 3? Why not just have one table friends, with friend ID and person_id? That makes querying easier, to start with. And in the long run, you WILL decide you want to add other features, and you do not have to modify the basic sctructure of the database.

Come to think of it... Friend are people jut like the owen oer the page. So you could store the friends in the general persons database as well, add a colum "Registered", which is 1 (yes) or 0 (no) and you can easily add all their details, when the need arises. Just modify the forms, and off you go.

ANyway.. These are just ideas. Have a think about it.

jbachris

leatherback wrote:
well.. For starters..

Your database is asking for trouble..

Why do you have a colum for friend1, 2, and 3? Why not just have one table friends, with friend ID and person_id? That makes querying easier, to start with. And in the long run, you WILL decide you want to add other features, and you do not have to modify the basic sctructure of the database.

Come to think of it... Friend are people jut like the owen oer the page. So you could store the friends in the general persons database as well, add a colum "Registered", which is 1 (yes) or 0 (no) and you can easily add all their details, when the need arises. Just modify the forms, and off you go.

ANyway.. These are just ideas. Have a think about it.

Sorry, should've explained my table layout better.

TABLE friends
friend_id (pk, int)
friend1_userid
friend2_userid
friend_active

The friend1_userid is a foreign key for member_userid AND friend2_userid is also a foreign key for member_userid.

Say you have these three people

John Smith (member_userid = 1)
Tommy Jones (member_userid = 2)
Aaron Thomas (member_userid = 3)

If John Smith (1) goes to Aaron Thomas' profile page and clicks the link 'Add John Smith To My Friends List' it will insert the following into the friends table:

friend1_userid = 1
friend2_userid = 3
friend_active = 0

So, the person that requests the friend relationship is friend1_userid and the person the request is sent to friend2_userid. The next time Aaron Thomas logs in, I have a query that will count where friend2_user = 3 AND friend_active = 0. This'll tell Aaron Thomas he has 1 New Friend Confirmation Waiting. If he clicks 'Accept This Friend Request' it'll set friend_active = 1, if he denies it it will delete the row from the table.

Now, on the individual's profile pages I want to list all of his/her friends. So, their member_userid will not always be in friend1_userid, but could be in friend2_userid.

I'm trying to run a query to find WHERE friend1_userid = GET $userid OR friend2_userid = GET $userid

I can run that query and get what I want. That's why I have the IF statement in the body, the issue I am running into is matching up friend1_userid IF friend1_userid != GET userid OR matching friend2_userid IF friend1_userid != GET $userid, with the name info from the members table.

leatherback

Aaahhh.. Ok, sorry about that!

jbachris wrote:
I can run that query and get what I want. That's why I have the IF statement in the body, the issue I am running into is matching up friend1_userid IF friend1_userid != GET userid OR matching friend2_userid IF friend1_userid != GET $userid, with the name info from the members table.

Here I am a bit confused again.. I am not sure what you are trying to say.. 😃

ANyway.. Let's rethink for a second.. What happens if Aaron sais "Sure, you can add me to your friends list, but I'd rather not put you on my friends list?".

I mean, even though it sounds silly, I am sure that there are these people out there (I would be one of them) who do not want long lists of friends on their site.

What I would do, is when Aaron confirms, have a checkbox saying "Ad john to my friends", and ALWAYS have friend 1 the requesting party, and friend 2 ALWAYS the to-be-listed friend.
I think that would solve your other problems too.

jbachris

leatherback wrote:
Aaahhh.. Ok, sorry about that!

Here I am a bit confused again.. I am not sure what you are trying to say.. 😃

ANyway.. Let's rethink for a second.. What happens if Aaron sais "Sure, you can add me to your friends list, but I'd rather not put you on my friends list?".

I mean, even though it sounds silly, I am sure that there are these people out there (I would be one of them) who do not want long lists of friends on their site.

What I would do, is when Aaron confirms, have a checkbox saying "Ad john to my friends", and ALWAYS have friend 1 the requesting party, and friend 2 ALWAYS the to-be-listed friend.
I think that would solve your other problems too.

hm, i think i'm going to do it the way, basically if you accept a friend request it automatically adds you to their friend list and them to yours.

so, the requesting party is always friend_1 and the friend-to-be is friend_2, that's how i have it setup right now.

when i run this query:

$friends_rs = $conn->Execute ( "SELECT * FROM members, friends WHERE friend1_userid = member_userid AND friend2_userid = member_userid ORDER BY RAND() LIMIT 3" ) or die ( $conn->ErrorMsg() );

it displays nothing.

if i switch it to:

$friends_rs = $conn->Execute ( "SELECT * FROM members, friends WHERE friend1_userid = member_userid OR friend2_userid = member_userid ORDER BY RAND() LIMIT 3" ) or die ( $conn->ErrorMsg() );

it displays both of them.

right now i have member_userid 1 set to have member_userid 2 as a friend.

so in the table i have:

TABLE friends
friend_id = 1
friend1_userid = 1
friend2_userid = 2
friend_active = 1

so, if i go to member_userid 1's page, on the friends list if I use AND in the query it displays nothing. if i use OR (like example 2) it displays 1 and 2, instead of just 2.