1

I have two mysql tables sent and queue. I insert members into the queue table. However I don't want to insert that same member again--if they're in the sent table. They were already sent an invitation.

How can I check if the member inserted into queue doesn't exist in sent ?

        if (insertEmail($email)) {
            echo "<span class='success'>Successfully added " . $email . " to the queue</span><br/>";
        } else {
            echo "<span class='fail'>Error we already sent " . $email . " an invitation email.</span><br/>";
        }

 function insertEmail($email)
    {
        mysql_query("INSERT INTO queue (email) SELECT `$email` FROM dual WHERE `$email` NOT IN (SELECT `email` FROM `sent`)");
    }

Quassnoi helped me get the query but I think I'm doing something wrong. When I try to insert emails into the queue I always get Unknown column '[removed]@gmail.com' in 'field list'

Community
  • 1
  • 1
Kyle
  • 3,004
  • 15
  • 52
  • 79

1 Answers1

3

Perhaps you don't have the ability to change your tables around, but if you do, have you considered creating a status column on the queue table and removing the sent table entirely?

The simplest possible version of this would be:

Table 'queue':

email              status

me@email.com       queued
someone@email.com  queued
another@email.com  sent

Then whenever your queue processor runs, the insertEmail becomes updateQueue($email) and your query is:

mysql_query('Update queue Set `status' = 'sent' Where email = '$email');

To retrieve anybody that has not yet been sent an email:

mysql_query('Select email From queue Where `status' = 'queued');
Jordan
  • 31,971
  • 6
  • 56
  • 67
  • Thats a good idea I didn't think of adding a status table... I'm going to do that now thanks a lot lol. How can I check if the email isn't already in the table tho? – Kyle Apr 19 '11 at 22:01
  • 3
    You may want to look at this answer for the full list of ways you could do that. http://stackoverflow.com/questions/763577/mysql-good-way-to-insert-a-row-if-not-found-or-update-it-if-it-is-found. I suggest that you just use the way the guy has been doing it in the question though. – Jordan Apr 19 '11 at 22:08