Two columns, two values

[shanparker]

I have this query semi finished. I have two tables. I need to query the first table for only entries where the "user" field is valid because it's a user in the "friends" table. I have that working. The third thing it needs to do is make sure that the user who is logged in is in friends table column.

Confusing.

The site I'm working on is a Myspace type site - so two entries go into the friends table when a friend link is created. An association each way for the friend. Friend 1 is friends with Friend 2, and a second one to show that Friend 2 is friends with Friend 1. The query I have written so far checks that they are friends - but it returns two results instead of one from the sticky_notes table because it's reading the two associations.

The query so far is:

PHP Code:

SELECT * FROM sticky_notes,friends WHERE friends.friend = sticky_notes.user LIMIT 5 


Now all I need to do is to get it to check for the third value. That the user (in the friends table) is the user who is logged in (which is the value $session->username).

I assume I'm on the right path here.

[guelphdad]

You shouldn't assume that if user 1 is a friend of user 2 it automatically makes user 2 a friend of user 1.

You should be adding a one way association in the table. (that is for the logged in user only tag the person they identify as a friend and not also doing the reverse).

From there if you want to see if user 1 is logged in and show all their friends, then you should be checking a cookie to see if the user is logged in.

then just do a look up in the table for all friends of that user.

[lavinpj1]

You need to look at JOIN sql statements here.

~Phil~

[guelphdad]

The query above is a join, it is just joined incorrectly. the = has to have the column from one table joined to the column on another but I believe it is joined on the particular userid.

select 
foo, 
bar 
from tableA, tableB 
where 
tableA.column1=tableB.column7

is still a join for instance, it is just in list join syntax. It is much easier to make errors in such format as seen above.

Better to use:

select 
foo, 
bar 
from tableA
inner join tableB
on tableA.column1=tableB.column7