A pretty complicated problem
Posted: Thu May 18, 2006 5:15 am
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.
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
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
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`
ASCNow 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());
}
}Hope I make sense