Split MySQL insert using PHP

PHP
1

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

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?

3

Thank you for your reply !

Yes, every subscriber will have 1 newsletter attached.

4

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;

5

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

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.

7

Thanks a lot ! This has been a great help for me !

Have a great day !
Mesjoggah
Netherlands