SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How to do the query

    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.

    So, any thought or ideas or queries are welcome.

  2. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Id doesn't look so complicated but it depends on how you manage your tables.
    Here's a quick start:
    Code:
    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

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vectorialpx View Post
    Id doesn't look so complicated but it depends on how you manage your tables.
    Here's a quick start:
    Code:
    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. :-O

  4. #4
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got it a bit wrong.

    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`).

    1. Get all companies and "Unsubscribed From User"
    Code:
    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
    2. Get al mails from `send_tbl`

    3. In PHP
    PHP Code:
    // 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 

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    First, you must think at the performance:
    1. How often will the cron run
    2. How many records are for users*mails*companies (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.

    Good luck!


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
  •