1

guys i really REALLY need some help on this one. 2 days i've been completely stuck. i need a direction to take this in because what i'm doing clearly isn't working and I'm getting very frustrated.

My overall goal is to throttle the amount of emails sent to destination domains if it is set in the database to be throttled. the reasoning behind this is to maximize the amount of email sent from the mail server + being able to adjust the throttle settings based on bounce rates, and other factors.

for example, if i set the throttle rate to '100' for gmail and yahoo, it will pull a max of 100 records LIKE 'gmail' and a max of 100 records LIKE 'yahoo' and proceed to send them. however, if there are no more throttled domains to process, pull $rest_max where they ARE NOT LIKE $throttle_domain and proceed to send them.

question #1 - how do i loop the first query over and over until $throttle_domain is exhausted?

question #2 - how would i pull records where they DON'T match the throttle domain and how would i tie that into this?

EDIT forgot to mention the below code works fine, except it will only pull 1 throttle record and stop.

        $rest_max = '200';

        // this is where i need to loop!?
        $query = "SELECT * FROM `mailer_lists` WHERE `email` LIKE '%".$throttle_domain."' LIMIT ".$trim_speed."" ;
        $result = mysql_query($query) or die(mysql_error());
        while($row = mysql_fetch_array($result)){
        $email = $row['email'];
        $project = $row['project_name'];

        $querya = "SELECT * FROM `mailer_controller` WHERE `project_name` = '".$project."'" ;
        $resulta = mysql_query($querya) or die(mysql_error());
        while($rowa = mysql_fetch_array($resulta)){
        $project_name = $rowa['project_name'];
        $from_name = $rowa['from_name'];
        $from_email = $rowa['from_name']."@".$node_domain;
        $subject = $rowa['subject'];
        $body = $rowa['body'];
        $content = addslashes($body);

    // set header
    $header_from = 'From: '.$from_name.' <'.$from_email.'>';
    $header_reply_to = '-f  '.$from_email;

    // send mail
    mail($email,$subject,$body,$header_from,$header_reply_to);


    // delete contact from list only if it gets sent.
    mysql_query("DELETE FROM mailer_lists WHERE `project_name` = '".$project_name."' AND `email` = '$email' ") or die(mysql_error());  
    }}
Improve this question
3
  • 1
    Why do you need to loop at all? You can just return everything with one query. Commented Apr 19, 2011 at 16:17
  • how would i go about that? i've read some posts mentioning the implode function inside a mysql query, but not sure how that would work in this situation. is that what you meant? Commented Apr 19, 2011 at 16:28
  • John, I'm sensing that you're new to this. ;) That isn't slander, we're all friends here. I also suspect that once you look at this problem from another angle you're going to slap your forehead and get a programmer levelup. I'm going to post an answer here because my code examples won't fit in the comment box, but I'm unsure as to whether it'll actually solve your issue. Commented Apr 19, 2011 at 16:44

3 Answers 3

Reset to default
3

This should remove unnecessary loops and extra queries, this may not solve all your answers, but may help you along the way.

I have not tested this code, so be sure to run it in a test environment first to make sure that I did not make a simple mistake which could lead to data loss, due to the nature of the queries, I put this disclaimer, TEST IT FIRST WITH TEST DATA PLEASE. 

    $rest_max = '200';

    $query = "SELECT * 
        FROM `mailer_lists` ml  
            JOIN `mailer_controller` mc ON ml.project_name = mc.project_name
        WHERE `email` LIKE '%".$throttle_domain."' LIMIT ".$trim_speed."" ;

    $result = mysql_query($query) or die(mysql_error());
    $delete=array();

    while($row = mysql_fetch_assoc($result)){
        $email = $row['email'];
        $project_name = $rowa['project_name'];
        $from_name = $rowa['from_name'];
        $from_email = $rowa['from_name']."@".$node_domain;
        $subject = $rowa['subject'];
        $body = $rowa['body'];
        $content = addslashes($body);

    // set header
    $header_from = 'From: '.$from_name.' <'.$from_email.'>';
    $header_reply_to = '-f  '.$from_email;

    // send mail
    mail($email,$subject,$body,$header_from,$header_reply_to);


    $delete[] = " (project_name = '$project_name' AND email = '$email') ";
}

if (!empty($delete)) {
    mysql_query("DELETE FROM mailer_lists 
        WHERE " . implode(' OR ', $delete)) or die(mysql_error());  
}

An easy way to test is comment out the mail part and change the DELETE FROM to SELECT * FROM and echo out what comes from the select to make sure the proper data that should have been deleted came out.

PLEASE READ BELOW

A better way to do the delete, however, is to use the Tables ID field and store that in the $delete. As that would alleviate the OR statement and minimize the error of accidentally deleting valid rows. Here is how that would work (just used the ending, replace ID with whatever your id field is:

    $delete[] = $row['id'];
}

if (!empty($delete)) {
    mysql_query("DELETE FROM mailer_lists 
        WHERE id IN(" . implode(', ', $delete) . ")") or die(mysql_error());  
}

UPDATE

I am not sure how fast this will run, etc. But one possible way to do it, without having it inside a loop is:

    // Fill the array however you want to with the domains. this is just an example
    $throttle = array('domain1.com', 'domain2.com', 'domain3.com');
    $query = "SELECT * 
        FROM `mailer_lists` ml  
            JOIN `mailer_controller` mc ON ml.project_name = mc.project_name
        WHERE `email` LIKE '%". implode("' OR `email` LIKE '%", $throttle) . "'  LIMIT ".$trim_speed." ORDER BY project_name, email";

Again this is untested, and I am not sure how performance wise it would match up. But something for you to test.

EDIT: Changed to fetch_assoc as apposed to fetch_array

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

ok first of all i absolutely love the way you made it into one query. my ONLY problem is $throttle_domain and $throttle_speed will potentially have 100's of values. should i loop it using foreach or could i implode the query so it will pull records for ALL the throttle domains, then mail them?
See update. I am not sure if that is the better way to go or just loop through the $throttle array. You will have to do some testing to see.
1

why don't you do something along these lines John:

    $rest_max = '200';
    $email = array();
    $project = array();

    $query = "SELECT * FROM `mailer_lists`, `mailer_controller` WHERE mailer_lists.project_name = mailer_controller.project_name AND `email` LIKE '%".$throttle_domain."' LIMIT ".$trim_speed."" ;
    $result = mysql_query($query) or die(mysql_error());
    $count = mysql_num_rows($result);
    while($row = mysql_fetch_array($result)){
    $email[] = $row['email'];
    $project[] = $row['project_name'];
    }
    $rest_max = $rest_max - $count;
    if($rest_max > 0)
    {
            //repeat the process with the new domain
    }

You can use a join to do both of your queries at once, and utilize the mysql_num_rows to see how many results were returned from the Select, and use this to determine if you've hit your 200 results yet. You could also put the whole thing in a for loop, but if you only use 2 domains as you mentioned above this would be the best method

Improve this answer

1 Comment

To get the emails not in the list you would just use NOT LIKE instead of LIKE
0

From what I see, you're doing this:

$query = "SELECT * FROM `mailer_lists` WHERE `email` LIKE '%".$throttle_domain."' LIMIT ".$trim_speed."" ;

where you could be doing this:

$query = "SELECT * FROM `mailer_lists` WHERE `email` LIKE '%$throttle_domain';";

I've replaced your concatenation ("string ".$variable." morestring") with inline variables ("string $variable morestring") because it's a little easier to follow without IDE highlighting. It's still correct syntax, and your PHP interpreter will be fine with it. But on to the actual change: the omission of the LIMIT clause.

By removing the LIMIT, you're returning everything at once. This is a Good Thing, because it means that instead of performing n queries every time the script is run, you're running two queries. It's also good because it gets you all your results at once.

You can then loop through the result with mysql_fetch_array, or you can - and this is awesome - use a function like mysql_fetch_all to turn the entire result into a standard array. For some reason mysql_fetch_all isn't part of the PHP MySQL module, but implementations are posted in the comments here:

http://php.net/manual/en/function.mysql-fetch-array.php

And then you'll have your entire search result as an easily-accessed, keyed array. Sexy and simple.

Improve this answer

2 Comments

the way i had it worked perfect. the issue i'm having is there could be 200 domains i'm throttling. which means $throttle_domain = 200 separate domains. only issue is i'm only performing 1 query so it will only pull 1 destination domain and mail to only one destination domain never to query the next destination domain. i just thought looping 200 queries at a time seems kinda ridiculous.
Looping through 200 queries at a time -is- going to put the hurt on your server if this script has to be run in parallel. How often will it be used? Is it an administrative tool that gets run occasionally or is it something that gets run all the time?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.