Managing newsletter signup (php, mysql)

Adam

Hello all,

I'm currently on to something new, newsletters. I've a site with member signup etc. and on the signup form i have a checkbox for whether or not the user would like to receive the newsletter. Obviously I then store the value in a database and select all from emails where newsletter = 1 blah blah blah

The complication arises when I expand it to topics. So the user checks the box to receive the newsletter, but then there are 10 more checkboxes for different topics within the newsletter. How do I manage this?

And I don't just mean database wise(though i'm not to sure on that, will i need a field for each checkbox), I mean how do I dynamically edit the content of each individual email based on each users topic preferences?

Thanks in advance!

muckwarrior

I guess you'd need to somehow split the newsletter up into different sections in accordance with topic and then pull in required sections when building the email that is to be sent to the user.

How is the newsletter composed?

Adam

Undecided on the composition yet, whatever method will best suit the requirements I suppose!

seamus

Database-wise, create a new table called "topic". Within this table, you'll have a field topicID.

Then you have another table, called "selectedtopic" or something. All this table will have is userID - topicID pairs, i.e.

+---------+----------+
| UserID  |  TopicID |
+---------+----------+
|         |          |
|    1    |    2     |
|    1    |    3     |
|    1    |    4     |
|    3    |    6     |
|    3    |    1     |
|    4    |    4     |
|    5    |    6     |
|    5    |    7     |
|    6    |    1     |
|         |          |
+---------+----------+

This is your list of what topics each user has checked.

Then in your script, load in all the topics to an array, and run a query which locates all users, and which topics they've subscribed to, e.g.

SELECT user.*, selectedtopic.topicID
WHERE user.newsletter AND user.userID = selectedtopic.userID
ORDER BY user.userID, selectedtopic.topicID

Then you iterate through the resultset and for each user, build the newsletter containing the topics they've selected, and send it off, then move onto the next user.

Adam

I suspected as much seamus, I was harbouring a small hope that there would be a less tedious method but I couldn't see any option beyond that! Ah well...

Thanks though!