2

Hi Im new to MySQL and PHP, and this is my first post.. So please bear with me. I am trying to add two tables and two rows from one table
friends 

member_ID | friend_ID | status

and a user table

member_ID | username

What I am trying to do is combine both tables for a friend request, friends.member_ID is the user.member_ID sending the request, friends.member_ID, is the user.member_ID that is being requested, status is when they accept the request it will turn 0 to 1 which will make the relationship true.

so far, I have this for my query to display all these fields to show who has requested this person as a friend

SELECT users.member_ID, friends.member_ID, friends.friend_ID, friends.status 
FROM `users` , `friends` 
WHERE friends.status=0   
AND users.member_ID = friends.member_ID
AND friends.member_ID = users.member_ID
AND users.member_ID = 6 (this will be $_SESSION[member_ID] when I add it to php)

I understand you can use an alias but I am a bit confused

Please help, my assignment is due tomorrow, and there is still so much to do.

Thanks

Sean
  • 12,443
  • 3
  • 29
  • 47
romie
  • 21
  • 1

2 Answers2

3

try this with join

 SELECT users.member_ID, friends.member_ID, friends.friend_ID, friends.status 
 FROM `users` 
 INNER JOIN `friends` 

 ON users.member_ID = friends.member_ID
 WHERE friends.status=0 
 AND users.member_ID = 6
echo_Me
  • 37,078
  • 5
  • 58
  • 78
0

Column alias - use AS(/as) after column name

users.member_ID as uId, friends.member_ID as fId

Table alias - define after table name

users u , friends f

If using table aliases, you can use them when selecting your column names

u.member_ID as uId, f.member_ID as fId

http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html

SELECT u.member_ID as uId, f.member_ID as fId, f.friend_ID as ffId, f.status as fStatus
FROM `users` u 
INNER JOIN `friends` f

ON u.member_ID = f.member_ID
WHERE f.status=0 
AND u.member_ID = 6

Aliases are useful when you are selecting 2 or more columns with the same name from multiple tables - users.member_ID, friends.member_ID. Instead of using an ambiguous $row['member_ID'] or having to use $row[0]/$row[1], you can use $row['uID']/$row['fID']

Sean
  • 12,443
  • 3
  • 29
  • 47
  • thanks this helps, the query comes with 0, but I think I have to play around with it. – romie Apr 06 '13 at 17:18
  • Could it be that you want `ON u.member_ID = f.friend_ID` as you are wanting `to show who has requested this person as a friend`? With `u.member_ID = f.member_ID` I think you are selecting where `u.member_ID` is the friend requester, not the friend requested – Sean Apr 06 '13 at 17:26