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