"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!
If it's based on an event - user added/updated then you can have the query as $sql = "UPDATE `{$users}` SET newsletter ='1' WHERE user_id = '{$user_id}' limit 1"; PHP:
@sarahk some of the users do not have an id. It would be ideal, unfortunately, I have users whose user_id is NULL.
I think I'm struggling a bit with your database structure If the email is in the users table then there must be duplicate user records - should the duplication issue be sorted out rather than working around it? If the duplication issue is ok then when you send the newsletter couldn't you just "select distinct email"? If the duplication issue is ok and the email is empty then the update query shouldn't be run - it should be checked in PHP before it's sent to MySQL.
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.
Aaah, then you may want to copy what I do in my big system - because the emails need to be personalised they need to be processed individually and therefore sent in batches to avoid timeout. I query the database for EVERYONE who should get the email and create a record in my `calls` table - this is where the distinct would be used I then query `calls` for x records and process - as they are processed their status is set to "sent" Benefits: I can monitor the send process, afterwards I can see who got sent an email and when people complain that they didn't get one I can prove that they were sent and track down the problems. I use Mandrill to do the actual send so I'm hitting their server with thousands of emails and they take charge of the throttled release of the emails as well as monitoring bounces, open rates. If I set things up even smarter I'd be able to query Mandrill and import that stuff but that's a job for another month. `calls` has a user_id and email_id so that I know which user got sent which email
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