循环一个mysql查询 [英] loop a mysql query

问题描述

伙计们，我真的真的需要一些帮助. 2天我已经完全卡住了.我需要一个指导，因为我明显在做的事情没有用，而且我感到非常沮丧.

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.

例如，如果我将gmail和yahoo的节流率设置为"100"，它将最多拉取100条类似"gmail"的记录，最多拉出100条类似"yahoo"的记录，然后继续发送.但是，如果没有更多受限制的域要处理，请将$ rest_max拉到与$ throttle_domain不一样的位置，然后继续发送它们.

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.

问题#1-如何反复遍历第一个查询，直到$ throttle_domain用尽?

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

问题#2-我将如何在与节流域不匹配的地方提取记录?我该如何将其绑定到其中?

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

编辑 忘了提一下下面的代码行得通，只是它只会拉1个油门记录并停止.

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

一种简单的测试方法是注释掉mail部分并将DELETE FROM更改为SELECT * FROM，并回显选择内容，以确保应删除的正确数据出来了.

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.

请仔细阅读

但是，执行删除的一种更好的方法是使用Tables ID字段并将其存储在$delete中.这样可以减轻OR语句的影响，并最大程度地减少意外删除有效行的错误.这是这样的工作方式(仅使用结尾，将ID替换为您的id字段为

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

---

更新

我不确定这将运行多快，等等.但是，将其放入循环中的一种可能方法是:

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.

编辑:已更改为fetch_assoc，与fetch_array

这篇关于循环一个mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！