I have a friendship table between users that looks like this.
CREATE TABLE user_relations ( pkUser1 INTEGER UNSIGNED NOT NULL, pkUser2 INTEGER UNSIGNED NOT NULL, pkRelationsType TINYINT UNSIGNED NOT NULL, PRIMARY KEY(pkUser1,pkUser2), FOREIGN KEY(pkuser1) references users(ID), FOREIGN KEY(pkuser2) references users(ID), FOREIGN KEY(pkRelationsType) references user_relations_type(ID) );
pkRelationsType is a pointer to another table that defines the type of relationship that users have (friendship (1), waiting (2) or blocked (3))
If user 1 is friend with user 2 , I have only one instance |1|2|1| and NOT also |2|1|1| .
The fact is that in order to block a user, I must keep in mind that a relationship can already be made (users can already be friends or even have a pending friendship notification), so I'm trying to insert data or update it if the relationship no longer exists .
I have this to send a friendship request, but it just ignores the insertion if the data already exists.
INSERT INTO user_relations(pkUser1,pkUser2,pkRelationsType) SELECT * FROM (SELECT :sender0,:target0,2) AS tmp WHERE NOT EXISTS (SELECT pkUser1 FROM user_relations WHERE (pkUser1= :sender1 AND pkUser2=:target1) OR (pkUser1=:sender2 AND pkUser1=:target2) LIMIT 1)
Due to the nature of the table, I cannot use INSERT ... ON DUPLICATE KEY UPDATE .
I was thinking about how to process it using PHP, look for relationships and order if it exists and then do one or the other, but this seems like a waste of processing.
Please note that I am not an expert on MYSQL, although I have still been doing this. Hope I explained quite well.
Thanks for the feedback.