Select from 2 rows ordering by a different Table

[ebolt007]

I have 2 tables I am trying to work with. One is a Users_Friends Table, and the other is the Users table. The Users Table holds the Names, that I want to sort by Alphabetically, this also has an ID that I am pulling the $user_ID from earlier in the code. When a user ads someone as a friend, it puts their UserID from Users into the Users_Friends.UserID Row and the person they are adding into Users_Friends.FriendID Row. Now if someone adds me as a friend, then I would be in the FriendID row instead of the UserID row that I would be in if I added them. I want to display all of these alphabetically, but when I use the below SELECT I wrote up, it gets the UserName for everyone from the UserID Row just fine, but if a few people added me, then it puts in my name a few times when I loop thru it and so all those names are all grouped together. I hope that makes sence? Basically, looping thru this, if My name is TESTMAIN_NAME and I have 5 friends, and I added 3 of them, and 2 of them added me, the return would look like,

 

a_friend, b_friend, e_friend,  TESTMAIN_NAME, TESTMAIN_NAME

 

when I need it to be a_friend, b_friend, c_friend, d_friend, e_friend

 

because that's alphabetical. I think I need an extra Left_Join, but can't figure out how to implement it, or I need an IF Users_Friends.FriendID = '$user_ID' only get Users_Friends.UserID ELSE IF Users_Friends.UserID ='$user_ID' only get Users_Friends.FriendID, but how would I write that?

 

SELECT 
                    Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName 
                    FROM 
                    Users_Friends 
                    LEFT JOIN Users 
                    ON Users_Friends.UserID = Users.ID 
                    WHERE 
                    Users_Friends.FriendID = '$user_ID' 
                    AND Users_Friends.Level = '1' 
                    OR Users_Friends.UserID = '$user_ID' 
                    AND Users_Friends.Level = '1' 
                    ORDER BY Users.UserName ASC

[kickstart]

Hi

 

Looks like an issue with the precedence of AND and OR.

 

Add brackets around the ORed items

 

(Users_Friends.FriendID = '$user_ID'

AND Users_Friends.Level = '1' )

OR  (Users_Friends.UserID = '$user_ID'

AND Users_Friends.Level = '1' )

 

All the best

 

Keith

[ebolt007]

No that didn't seem to change anything, I thought maybe the Brackets would only be unique or something. Basically I have my table settup like below.

Say the User table has.

ID-----------UserName----------

1--------------a_friend----------

2--------------b_friend----------

3--------------c_friend----------

4--------------d_friend----------

5--------------e_friend----------

6--------------f_friend----------

 

Now lets say user 3 ads 3 of the people, and 2 of the people add him as a friend. My Users_Friends table is

UserID--------FriendID-----------Level

3-------------------1-----------------1----

3-------------------2-----------------1----

3------------------5------------------1----

4------------------3------------------1----

6------------------3------------------1----

 

Now using the code below, it will take the UserID from the Users_Friends then sort it by the Users UserName field, but that will then give us the first 3 returns as ID 3 or UserName c_friend, but I need the code, to look at the FriendID is the UserID == '$user_ID' and if the FriendID == '3' I need it to only grab the UserID in the same SQL Query so I can sort by all of those unique usernames.

 

[fenway]

Well, you definitely need the parentheses -- don't kid yourself.

 

Also, since you're not using the LEFT JOIN, switch it to INNER JOIN.

 

Is the query returning the right information or not?

 

 

[kickstart]

Hi

 

Getting rather confused over what you want.

 

It seems that what you want is 2 queries unioned together, so you can sort the values.

 

Nothing in he code you posted earlier has anything that will sort them except in user name order.

 

My guess at what you want (and it is a guess, and I might be checking the friend id / user id the wrong way round) is something like this:-

 

(SELECT '1' AS SortCode, Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.UserID = Users.ID 
WHERE Users_Friends.UserID = '$user_ID' 
AND Users_Friends.Level = '1')
UNION
(SELECT '2' AS SortCode, Users_Friends.FriendID, Users_Friends.UserID, Users_Friends.DateRequested, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.FriendID = Users.ID 
WHERE Users_Friends.FriendID = '$user_ID' 
AND Users_Friends.Level = '1') 
ORDER BY SortCode, UserName ASC 

 

All the best

 

Keith

[fenway]

I suppose you could use an IF() to determine this in a order by clause inline, but this is much cleaner.

[ebolt007]

That still returns the same thing Kickstart, it checks to see if both tables have my UserID in it, and returns the correct amount of friends, but when I try to view the Username it displays, (Mine123 is my username from the User table)

Anothernewtest, Mine123, GuyDefault, testgirl

 

when it should return (because j_test is the username of someone that added me as a friend.)

Anothernewtest,  GuyDefault, j_test, testgirl

So it's looking thru both tables to make sure my ID = "1" (which is my test ID) but then it only displays the UserName based on the ID's in the UserID column, it seems like I definately need an IF statement, but only for the feedback from ORDER BY Users.Username ASC, Like an IF UserID = '$user_ID' then only grab the FriendID

 

Here's what I use to get the above statement.

$true_query = mysql_query("
                    SELECT  
                    Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName 
                    FROM 
                    Users_Friends 
                    LEFT JOIN Users 
                    ON Users_Friends.UserID = Users.ID 
                    WHERE 
                    (Users_Friends.FriendID = '$user_ID' 
                    AND Users_Friends.Level = '1') 
                    OR (Users_Friends.UserID = '$user_ID' 
                    AND Users_Friends.Level = '1') 
                    ORDER BY Users.UserName ASC
                    ");
                    
                     $is_odd_row = 1;
                     while ($true_row = mysql_fetch_assoc($true_query))
                      {
                        $friend_user1 = $true_row['UserID'];
                        $friend_user2 = $true_row['FriendID'];
                        $namemain = $true_row['UserName'];

                      	echo "$namemain, ";

}

 

Sorry, I'm trying to explain the best I can.

[kickstart]

Hi

 

Can you list  the records going in and coming out, and what order you expect them in.

 

From your description I am not sure which of your output records is from where on the friends table.

 

Also check by swapping the fields checked by the 2 where clauses.

 

(SELECT '1' AS SortCode, Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName

FROM Users_Friends

INNER JOIN Users ON Users_Friends.UserID = Users.ID

WHERE Users_Friends.UserID = '$user_ID'

AND Users_Friends.Level = '1')

UNION

(SELECT '2' AS SortCode, Users_Friends.FriendID, Users_Friends.UserID, Users_Friends.DateRequested, Users.UserName

FROM Users_Friends

INNER JOIN Users ON Users_Friends.FriendID = Users.ID

WHERE Users_Friends.FriendID = '$user_ID'

AND Users_Friends.Level = '1')

ORDER BY SortCode, UserName ASC

 

All the best

 

Keith

[ebolt007]

Ok, so here is my Users Table

ID

UserName

1

Mine123

2

Anothernewtest

3

GuyDefault

4

testgirl

5

j_test

 

then here is my Users_Friends table

 

UserID

FriendID

Level

4

1

1

3

1

1

1

5

1

2

1

1

 

 

 

 

So I need to loop thru, and get any ID from UserID or FriendID that is set as '1' because those are then my friends. They are on different sides, because if I ad someone and I am logged in, it takes the logged in persons ID and always puts it in UserID, then the friend being added in the FriendID, but if someone else is logged in it takes that persons ID and puts it in UserID and yours in FriendID because they are then doing the requesting. Ok, so I need to grab anything from either Row, then get and sort by the Names of the corresponding UserName Row in Users Table., and the code I have tho, only selects from the UserID row, so it then outputs myself for the 3rd column down 1,5,1 it outputs Mine123 when it should out put j_test because it has to see that it's not looking for ID 1. Hope that's a little more clear, thanks for working with me on this.

[ebolt007]

Sorry, forgot to put how I want it to output, I want it alphabetically from the Users table, but since I am only getting the names from the IDS in the UserID Friends Table I am getting

Anothernewtest

GuyDefault

Mine123

testgirl

 

when I need it to be

 

Anothernewtest

GuyDefault

j_test

testgirl

 

because if I get a bunch of people asking me to be friends, then there would be a bunch of ID's of '1' in the UserID table, so those would all be grouped together, basically under M for Mine123, but they need to be under their own letters, so that's why if UserID ='1' then it should grab the Users with the ID from FriendID ELSE grab the UserID if it != '1'.

[kickstart]

Hi

 

Right, get it now. I thought you wanted them sorted within whether they were users or friends, but you just need them sorted overall:-

 

SELECT Users_Friends.UserID, Users_Friends.FriendID, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.UserID = Users.ID 
WHERE Users_Friends.FriendID = 1 
AND Users_Friends.Level = '1'
UNION
SELECT Users_Friends.FriendID, Users_Friends.UserID, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.FriendID = Users.ID 
WHERE Users_Friends.UserID = 1 
AND Users_Friends.Level = '1' 
ORDER BY UserName ASC

 

All the best

 

Keith

[ebolt007]

Perfect, works like a charm! Thanks so much, sorry my explanation was so confusing at the begging, I really appreciate the help!