插入但忽略如果重复AND如果存在于另一个表中则设置不同的值

Question

I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array containing userIDs from friends that the user invited. I wish to loop through them and insert the invitations into a database. The looping and all is already under control but the database query isn't.

This is what my current query looks like:

SELECT `iID` FROM `invitations` WHERE `iSender` = 'SENDER' AND `iReceiver` = 'RECEIVER';

If that returns zero rows I process this query:

INSERT INTO `invitations` (`iSender`, `iReceiver`) VALUES ('SENDER', 'RECEIVER');

And then I check if they're already signed up to my website:

SELECT `uID` FROM `users` WHERE `uOAuthID` = 'RECEIVER';

If it returns more then 1 row I run the following and final query:

UPDATE `invitations` SET `iProcessed` = 1 WHERE `iReceiver` = 'RECEIVER';

So basically this is how the process is currently shaped:

1. If the user hasn't already has been invited by the inviter we insert the invitation into the database.
2. Then we check if the invited user already is signed up.
3. If he is signed up we update the invitation and say that it already has been processed.

I guess there's a better and faster method to do this with just maybe 1 or 2 queries. I've tried using INSERT IGNORE and ON DUPLICATE but that just gave me errors and gave up.

I hope that you understand what I'm looking for. Thank you all for your time!

Answer 1

There are to thing that suite your needs:

INSERT ... ON DUPLICATE KEY UPDATE Syntax:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

INSERT IGNORE:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs.