Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Sep 2004
    Posts
    54
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Two columns, two values

    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.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Sep 2005
    Posts
    394
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You need to look at JOIN sql statements here.

    ~Phil~

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    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:

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •