loop a mysql query

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

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

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

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.