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:
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!
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.