Problem with Joins

[joachimanderson]

I am building a site where members can create profiles that other members can view. I want to add a favourites feature to the profiles so a member can keep a list of their favourties members. This all works fine but now I want to find a way that a members can see ALL the members on the site and can see if they have already added them to their favourites.

I have two tables; members and favourites.
They look something like this but I've simplified them and this is the query I'm using:

SELECT members.*, favourites.* FROM `members` LEFT OUTER JOIN `favourites` on members.username = favourites.user_name WHERE members.username

Members
userid----------------username-------
1                       Bob
2                       David
3                       Sue

Favourites

userid-----------------------user_name-----------------------------favoured_user
1                              Bob                                       Sue
3                               Sue                                      David
2                               David                                    Bob

I have been trying to do this by using an outer join on members.username and favourites.user_name but I get duplicate entries for members that have selected more than one favourite (this I believe is how an outer join should behave) but I want to retrieve all members from the table and only show which members have been favoured. I hope I'm making myself clear.

Thanks,

Julian

[urtrivedi]

1) what are table strutures of both tables?
2) You want to search fav of one particualar user or all users?
3) What query output you expect for your above sample records?

[Taywin]

a members can see ALL the members on the site and can see if they have already added them to their favourites

Well, your requirement seems to be 2 parts -- select all members but self and check for any members that are not in favourite. You may be able to do this through query (but I am still not sure you could it efficiently), but it is going to be an overkill to your DB server due to JOIN command; especially, if your tables gets big and you try to join ALL members. If you can do it on your server, why not devided the process into 2 parts instead?

[joachimanderson]

Well that sounds interesting. Like two different queries and let php sort out the rest?

Here is the php code I am using to display if a member retrieved from the database has already been marked as a favourite by the visitor and this works well... but the problem is with the join code above I seem to be getting all entries PLUS the favourites. I am a real novice at this stuff so the solution to this has really stumped me.

?php
$visitor = $_SESSION['username']; 
 while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
if ($row['user_name'] == $visitor){  
  $msgs = "favourite"></span>";
}
else {$msgs = "x";
}
?>

[pritaeas]

You may be able to do this through query (but I am still not sure you could it efficiently),

I think using a query would still be more efficient than getting all data and then parsing it in PHP yourself.

To give the right query, show the table structures and expected output for the above (as utrivedi already asked).

[Taywin]

Oh, I misread the requirement. Well I could assume the table structure from what the OP said. Simply create 2 tables -- members and favourites. The members table has 2 fields -- user_id and user_name. The favourites has 2 fiels -- user_id and favour_id. That should be enough to show a query to do the work.

Then I came up with below for user id 1...

SELECT members.*
  FROM members
  WHERE members.user_id NOT IN
  (SELECT favourites.user_id
     FROM members LEFT JOIN favourites on members.user_id!=favourites.user_id
     WHERE members.user_id=1 and favourites.favour_id=members.user_id) and members.user_id!=1

Not sure it is exactly what the OP wants...

The table structure look mighty odd to me. I'd go with...

members: id (PK) | username
faves: id | id2 (compound PK of both fields)

You shouldn't include duplicated data in your related tables - it's a recipe for disaster. If you update your member name in the members table, the member name in the favourites table remains unchanged - bad.

[joachimanderson]

THank you for all your replies. Here is the current table structure for 'members' and 'favourites'. I must stress I am a novice so I apologise for any disastrous logic.

Members

Field-------Type-------NUll----------Key-------Default------Extra
id ---------int(11)-----no-----------PRI-------null-----------ai
username----varchar(255)--no-------------------null`




Favourites

Field-------Type-------NUll----------Key-------Default------Extra
id----------int(11)-----no-----------pri---------null--------ai
user_name--varchar(255)-no---------- pri---------null
favourite_id--varchar(--no ----------------------null
userid-----------varchar(255)--------no ---------null

The expected output I want is when a logged in member retrieves a list of all the other members in the database those that he has marked as favourite are shown as so but also those that have not been marked as a favourite are also shown. Therefore if there are six members in the database and two of them marked as favourites then 6 will be listed and two of those are marked as a favourite. Something like this:

Peter is logged in and has marked Susan and Bob as favourites.

userid-------------username-----------favourite_id----------
1--------------------Peter --------- ---Susan            
3--------------------Susan-----------------------------------
6--------------------John--------------------------------
8--------------------Henry-------------------------------
11-------------------Bob-----------------Bob------------
10-------------------Kate---------------------------------

I won't comment about the data integrity again. Up to you how you do it.

If you are logged in, you should only see your own favourites (among the full list of members). Seeing other people's friendships should only be possible if you have permission to see their "profile page" if they have one.

So a typical query using a relationship / join could output...

id | fave | username
____________________

1  | 1    |  mike (my friend)
2  | NULL |  ollocks
3  | NULL |  diafol (ME)
4  | NULL |  foo
5  | 1    |  melon (my friend)
6  | NULL |  dave
7  | NULL |  bryn
8  | 1    |  mandy  (my friend)
9  | NULL |  dai
10 | NULL |  steve

That could be modified in a resulting code loop or even in the query itself to give:

mike*
ollocks
diafol
foo
melon*
dave
bryn
mandy*
dai
steve

Don't know if that helps

[joachimanderson]

Thank you for this. I shall have a go and report back.

Kind regards,

Julian