SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Split MySQL insert using PHP

    Hi,

    On our website i have installed a newsletter add-on.
    All the emailadresses (3000) of the recepients are stored in a table called: jos_jnews_subscribers
    The recepients are identified by a subscriber_id

    There also a table called jos_jnews_listssubscribers.
    In this table are the recepients that are "member" of a certain newsletter.
    The newsletter id's are stored in a column called list_id
    To make a user member of a newsletter, I must connect the subscriber_id to a list_id.

    There are like 10 newsletters, identified with this list_id.
    My problem is that I have one table with all the recepients and i have like 10 newsletters.
    I know how to insert data into a table, but i do not know how i can split an insert.
    I would be great if I could manage to make the first 250 recepients, member of list 17,
    The following 30 member of list 18 and the next 125 of list 19.

    From another member of the Sitepoint forum I understood that the easiest way to do this, is by using PHP.
    Is there someone who can reach me a helping hand on yhis matter ?

    Thanks
    Mesjoggah

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    To make a user member of a newsletter, I must connect the subscriber_id to a list_id.
    Will every subscriber only have 1 newsletter attached to them?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply !

    Yes, every subscriber will have 1 newsletter attached.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    K. jos_jnews_listssubscribers is a superfluous table then; you could store the value as a field on jos_jnews_subscribers instead.
    I would be great if I could manage to make the first 250 recepients, member of list 17,
    The following 30 member of list 18 and the next 125 of list 19.
    Assuming that the subscriber_id is an autoincrementing field starting from 0 with no gaps: (and that can be a very large assumption!)
    UPDATE jos_jnews_subscribers SET newsletter_id = 17 WHERE subscriber_id < 250;
    UPDATE jos_jnews_subscribers SET newsletter_id = 18 WHERE subscriber_id BETWEEN 250 AND 279;
    UPDATE jos_jnews_subscribers SET newsletter_id = 19 WHERE subscriber_id BETWEEN 280 AND 404;
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you !

    If subscriber_id starts at, let's say 30. Can I start in that case with:
    UPDATE jos_jnews_subscribers SET newsletter_id = 18 WHERE subscriber_id BETWEEN 30 AND 250;

    Or do if always have to start with the < line ?

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    They're seperate commands. You can do any or all of them. the < would actually still work (since there's nothing lower than 30, nothing gets overwritten.

    You should NOT do:
    UPDATE jos_jnews_subscribers SET newsletter_id = 17 WHERE subscriber_id < 250;
    UPDATE jos_jnews_subscribers SET newsletter_id = 18 WHERE subscriber_id < 279;

    because when the second line executes, it will overwrite everything below 250 as well.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot ! This has been a great help for me !

    Have a great day !
    Mesjoggah
    Netherlands


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •