Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.