I am working on a new thing. It’s like a table with a lot of companies and one table with a lot of users.
The users can write a little newsletter and my page will send it to the companies in the company table that are in the chosen type of business.
When the user is writing the text a table called send_tbl will be used. This is where all the users newsletters are stored with all the info for the newsletter.
When everything is done, the picked companies will be in a new table and set as a cron job sending out a bunch of newsletter every 10 mins or so.
I will also make a table where I store a list of companies that unsubscribe.
A company can unsubscribe to all newsletters, then it’s set to 1 in the unsub field in the company table.
But, they can also unsub from a specific user only. So, if they unsub from a newsletter that is being sent, I thought about building a separate table containing all the single unsubscriptions. So, if a company is clicking the “unsub from this user” button in a newsletter from user 1240, then I will add comp_id and user_id in a table called unsub_tbl.
The first table of the companies contains | comp_id | comp_name | comp_email | comp_type | comp_unsub | and so on…
The send_tbl contains | send_id | user_id | news_text | and so on…
And finally the usnsub_tbl contains | user_id | comp_id |
So, when a newsletter is being sent it has to check wether the type of company is what the user picked. It also has to check that the company haven’t unsubscribed from all newsletters in my db.
Then it has to check if the company haven’t unsubscribed from only this user (in the unsub_tbl).
And when these things are checked, then they should be added to a new table called pipeline_tbl which contains |send_id | comp_id | and so on… and this table is the one I will pick the parts that will be sent using a cron job.
Is this a good way to do it. And how would a query look to check these settings so it runs through the unsubscription parts the correct way.
This is all in my head only right now, but I don’t really think I know if it will work and how to make it work this way.
Id doesn’t look so complicated but it depends on how you manage your tables.
Here’s a quick start:
SELECT comp.comp_email
FROM pipeline_tbl pp
INNER JOIN companies comp
on pp.comp_id = comp.comp_id AND comp.comp_unsub = 0
# I think you also need some JOIN [ company => user assoc ]
# OR, maybe every user may send to any company
LEFT JOIN usnsub_tbl unsb
ON unsb.comp_id = comp.comp_id
WHERE unsb.user_id IS NULL
# however, you must have your pipeline_tbl populated properly
Well, actually, what is ending up in the pipeline_tbl would be the ones that are being sent.
So, I want to find the ones that aren’t unsubscribing from everything (from comp_tbl) and/or from the specific user (from unsub_tbl) and THEN they should end up in the pipeline where everything stored are ones that should be sent.
Then I don’t think your solution would be right? Or am I missing something or maybe I forgot to write that in my post.
Again, You need to send from all users to all companies, without those that are unsubscribed (in comp_tbl.comp_unsub or usnsub_tbl).
So, in the end, you need a list of users that, each, have a list of companies mails to send some newsletter?
I think (just me) it’s better to have the list with all companies, excepting those that are unsubscribed (you exclude them from the start) and from PHP you send from every user only to those that are not unsubscribed (in usnsub_tbl).
Get all companies and “Unsubscribed From User”
SELECT
comp.comp_email,
unst.user_id UnsubscribedUser
FROM companies comp
LEFT JOIN usnsub_tbl unst
ON unst.comp_id = comp.comp_id
WHERE
comp.comp_unsub = 0
Get al mails from send_tbl
In PHP
// we have $ALLCOMPANIES = result of the first SQL
$mailsToSend = array();
foreach( $ALLMAILS as $mail ) {
foreach( $ALLCOMPANIES as $compDetails ) {
if( $compDetails['user_id'] != $mail['UnsubscribedUser'] ) {
$mailsToSend[$mail['user_id']][] = $compDetails['comp_email'];
}
}
}
// we have $mailsToSend
Well, the list of all companies are for all users. But they are specialized in different areas.
So, maybe user Bob wants to send a newsletter to all garden companies in the list. But not the ones that have unsubscribed marked as 1 and not to the companies that sent an unsubscribe to Bob last time he sent a newsletter.
The company table have a lot of info about the company and the user table as well. A lot. But I think the only fields that might be used here are the ones I wrote about in my example.
I thought about building a temporary table sending all that matches the type of company and that have nothing added in the unsubscribe field.
Then have another query picking all those and check if they are not unsubscribing newsletters from this specific user.
But I think I am doing something more than I need to. I usually think about programming the wrong way. I’m more a designer type and usually do the programming part a little strange, but usually it works in the end. But this time I think there might be a better solution that someone can help me with.
How many records are for usersmailscompanies (you’ll generate a combination of records for each)
That temporary table will get huge in case we’re talking about millions of records and, the server will need a breath to create it and delete it. It will also be locked for a wile.
So, I’d take the classic way: some SQL that include conditions. In my example you’ll interrogate the companies table and the send_tbl one only once, the rest of the job being done by the PHP. Placing a sleep once a hundred thousands records will help the server take a breath and make things in peace.
So, my idea is to include in the first SQL your other options (categories and others) and let PHP do the sorting with simple IFs.
Maybe someone else will give you a better idea.
