循环一个mysql查询

Question

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());  
    }}

Answer 1

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