A rather complicated task

[php_nub_qq]

Good day fellow programmers.

 

I need to alter the following table:

CREATE TABLE `friendships` (
 `sender` int(11) NOT NULL,
 `recipient` int(11) NOT NULL,
) 

so that sender and recipient cannot be the same person ( same ID ) neither can they repeat no matter who is who. I suppose you didn't understand me so I best give an example:

sender	recipient
1	2        << OK
2	1        << NOT OK - record already exists in opposite order
1	3        << OK
1	5        << OK
2	3        << OK
2	5        << OK
3	3        << NOT OK - same ID

I find it hard to believe that it is achievable without the contribution of great PHP but I thought I should ask before proceeding. Anyone?

[Psycho]

What do you mean by "alter" the table? Are you wanting to modify records already in the table, are you wanting to modify the table schema to prevent those conditions (don't think it's possible)  or are you wanting to create code to prevent those "error" conditions. Both are very easy.

[php_nub_qq]

I guess I didn't express myself correctly. I want to make those types of records ( the NOT OK ones ) uninsertable, such as when you try to duplicate a unique field.

[Jessica]

Why?

[php_nub_qq]

Because if there is a way I can do this then I don't need ot have series of IFs in my php + I suppose it is more professional that way and believe it or not that's what I'm aiming for

[Jessica]

You should have a function for getting and setting friendships, so the code would only have to be in one place.

If you do it in mysql (which you can't anyway, not possible), your insert would fail, giving you LESS information than your few conditionals will.

[Psycho]

You can put a unique constraint on a combination between multiple fields. So, you would not be able to enter two records which have the same value for sender and the same value for recipient. But, there is no way I know of through the DB schema to prevent two records where the values are swapped between the fields in the two records (e.g. same as the second record in your example).

 

Why not do a simple SELECT before inserting the reocrd? No need for a bunch of if()'s in your code.

I can think of some more complicated solutions, such as creating a trigger that creates a unique value based upon the two IDs and sets that value in a third field with a unique constraint. But, really that is overkill.

[Psycho]

Run this query first. If the count is not 0 then don't insert

 

SELECT COUNT(*)
FROM `friendships`
WHERE (`sender` = $senderID AND `recipient` = $recipientID)
   OR (`sender` = $recipientID AND `recipient` = $senderID)

[php_nub_qq]

Well then I guess it's the power of PHP again. Thanks guys!