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:
- If the user hasn’t already has been invited by the inviter we insert the invitation into the database.
- Then we check if the invited user already is signed up.
- 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!
You can try in two steps as
INSERT IGNORE INTO `invitations` (`iSender`, `iReceiver`) VALUES ('SENDER', 'RECEIVER');
This will ignore if the record already exists.
Then update with by joining the two tables
UPDATE `invitations` as i INNER JOIN users AS u on i.iReceiver = u.uOAuthID SET `iProcessed` = 1 ;