1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Update only the first occurrence

Discussion in 'PHP' started by qwikad.com, Oct 30, 2016.

  1. #1
    "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!
     
    qwikad.com, Oct 30, 2016 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,052
    Likes Received:
    4,362
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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:
     
    Last edited: Oct 30, 2016
    sarahk, Oct 30, 2016 IP
  3. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,081
    Likes Received:
    1,626
    Best Answers:
    27
    Trophy Points:
    475
    #3
    @sarahk some of the users do not have an id. It would be ideal, unfortunately, I have users whose user_id is NULL.
     
    qwikad.com, Oct 30, 2016 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,052
    Likes Received:
    4,362
    Best Answers:
    123
    Trophy Points:
    665
    #4
    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.
     
    sarahk, Oct 30, 2016 IP
  5. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,081
    Likes Received:
    1,626
    Best Answers:
    27
    Trophy Points:
    475
    #5
    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.
     
    qwikad.com, Oct 30, 2016 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,052
    Likes Received:
    4,362
    Best Answers:
    123
    Trophy Points:
    665
    #6
    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.
    1. 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
    2. 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
     
    sarahk, Oct 30, 2016 IP
    qwikad.com likes this.
  7. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #7
    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.
     
    PoPSiCLe, Oct 30, 2016 IP
  8. Einheijar

    Einheijar Well-Known Member

    Messages:
    539
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    165
    #8
    
    "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.
     
    Einheijar, Oct 31, 2016 IP
    qwikad.com likes this.
  9. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,081
    Likes Received:
    1,626
    Best Answers:
    27
    Trophy Points:
    475
    #9
    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.
     
    qwikad.com, Oct 31, 2016 IP
  10. Einheijar

    Einheijar Well-Known Member

    Messages:
    539
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    165
    #10

    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
     
    Einheijar, Oct 31, 2016 IP
  11. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #11
    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.
     
    PoPSiCLe, Oct 31, 2016 IP
  12. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,081
    Likes Received:
    1,626
    Best Answers:
    27
    Trophy Points:
    475
    #12
    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.


     
    qwikad.com, Oct 31, 2016 IP
  13. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #13
    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 ;)
     
    PoPSiCLe, Oct 31, 2016 IP