Page 1 of 1

A pretty complicated problem

Posted: Thu May 18, 2006 5:15 am
by s.dot
I have a table for 'friends' that shares a record for both users.

Consider the following.

ID FRIEND1 FRIEND2
1 bob sue


Bob & sue have a friend relationship. To show this to both users I do a union query.

Code: Select all

(SELECT
  `id` AS `id_field`,
  `friend1` AS `user`
FROM
   `friends`
WHERE
   `friend2` = '$myself')
UNION
(SELECT
   `id` AS `id_field`,
   `friend2` AS `user`
FROM
   `friends`
WHERE
   `friend` = '$myself')
ORDER BY
   `id_field`
ASC
This works good and will pull out an entire 'friends list' for each person, sharing records.

Now my problem comes when I add a `rank` field to the table and I want bob & sue to be able to order their list the way they want.

Obviously I'd need two fields. rank1 and rank2 so bob could order his list the way he wants, and sue could order hers the way she wants.

My problem is knowing which field (rank1 or rank2) to update in my queries, since we're sharing a single record.

My logic is something like this

If myself == friend1, update rank 1, otherwise i must be friend2 so update rank2

my initial query is this

Code: Select all

foreach($finalOrder AS $key=>$id){
	if(is_numeric($key) && is_numeric($id)){
		mysql_query("UPDATE `friends` SET `rank` = '$key' WHERE `id` = '$id' LIMIT 1") or die(mysql_error());
	}
}
But then I realized if bob were to run this query, it would sort his list quite nicely. But then sue goes to sort her list, and it would mess up bobs entire order! Thus i need to update the appropriate `rank(1|2)` field.

Hope I make sense :?

Posted: Thu May 18, 2006 6:47 am
by BadgerC82
Hey man,

Would you not be best just to create a seperate table to link the rank to the friends? And then just have the rank id in the new table with the id of the user and the id of the friend list?

Either that or split the friend table like this.

Friend_id
user_id
rank_id
??

Not sure if this helps.. maybe I don't understand the problem fully :)