Update only the first occurrence

"UPDATE $users SET newsletter ='1' WHERE email = '".$data['email']."'";

This sets the newsletter to 1 every time the email is added. I want it to be set to 1 only for the first occurrence of an email address and then it should be ignored. How would I do this?

Just to make it clear:

Now:

email..................................newsletter
----------------------------------------
............... 1
............... 1
............... 1
............... 1
............... 1
............... 1

I want:

email..................................newsletter
----------------------------------------
............... 1
...............
...............
............... 1
...............
...............

Thanks!

 

@sarahk some of the users do not have an id. It would be ideal, unfortunately, I have users whose user_id is NULL.

 

Wow, you're good. That's my issue. I have too many subscribers to email all at once. I want my list to be divided into batches. If I try "select distinct email" with each new batch, inevitably the same users are being emailed several times. So... I wanted to make sure that the record is entered just once for each email beforehand.

 

Uhm... you can use "select distinct..." together with limit and starting point, like in a pagination set. I don't see the problem. And I still don't understand why you have multiple instances of the _same_ email. An email should only be registered once.

 

"UPDATE $users SET newsletter ='1' WHERE email = '".$data['email']."' limit 1";

Code (markup):

The limit still applies although if there are other fields in the table you may want to order by those to get more consistent(?) results.

 

Pop, I think sarahk meant "select distinct" when you email your list. I have a script that does that using "select distinct", however, as I said up there, the list grew too big to email it all at once. The other day I sent out an offer sending emails in batches and then realized that I emailed the same users several times. Needless to say I was a bit ticked off.

 

Why not fork it into a background process and output to a log file. That's what I do for fairly long scripts. nohup php /path/to/script & then parse nohup.out or any other file you use to log

 

Yes, hence I said use limit with counter, and send for each iteration of the result. How was that not obvious? And I still wanna know why you have multiple instances of the same email. I think that means your database needs at best a cleaning, at worst a redesign.

 

Pop, I do need to record emails every single time. That's not just a "sign up for a newsletter" script. It does a host of other things that require all emails (even duplicate ones) to be stored in the DB. I just didn't know I had to mention that when I posted the question.

I got it all figured out. It simply records the first instance as 1 and all consecutive ones as 0. Simple, yet it works. Sometimes I look back at some of the questions I asked here and I go "all I needed was a couple more hours and I'd figure it out on my own". You know how it always happens.

 

That is true. I still think you need to rethink the DB setup, though. Again, it shouldn't be needed to record the actual email every time. Maybe track a counter, or if you need dates for each time, run a separate table with userid, login, recorded_date or something (user_id would then link to the email). The point is, in a database, an email should be almost as unique as a user_id