PHP PDO fetch results

Question

I have the following script which runs fine but I think

a) it is ugly;

b) it doesn't do what I need after the script is run.

Utlimately, the script should work as follows:

1) SQL selects all agents from a given table where the enrollment date is within the last month, grouped together by agent number where the count is greater than 1 ordered by enrollment date in ascending order. In short English, return a list of agents who have enrolled more than one member in the previous month.

2) Once the results are returned, the entire list is emailed to myself (not sure how to accomplish this yet) and the agent prize table is updated (agents enrolling more than one member get a prize).

To accomplish this, my script currently runs back to back foreach loops (which I hate) in order to break out the necessary field data.

Any suggestions for improving the process are appreciated.

SCRIPT:

<?php

    try {   
            $db = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->beginTransaction();

            $sql = $db->query("SELECT AGENT FROM tablename WHERE enroll_date > DATE_SUB(CURDATE(),INTERVAL 1 MONTH) GROUP BY AGENT HAVING COUNT(AGENT) >= 0 ORDER BY enroll_date ASC");             

            $result = $sql->fetchALL(PDO::FETCH_ASSOC);
            foreach($result as $key=>$val) {
                foreach($val as $ball=>$agentid) {

                    $sql2 = "UPDATE agent_prizes SET prize=?, method=? WHERE AGENT = '$agentid'";

                    $stmt = $db->prepare($sql2);
                    $stmt->execute(array("Y", "COMP"));


                    require('../phpmailer/class.phpmailer.php');

                    $mail             = new PHPMailer();
                    $mail->Username   = "[email protected]";
                    $mail->Password   = "";
                    $mail->SetFrom("[email protected]");
                    $mail->Subject    = "Subject";
                    $mail->MsgHTML("Message");

   //I NEED TO RUN YET ANOTHER QUERY SELECT ABOVE TO CAPTURE THE AGENT EMAIL ADDRESS, LETTING THEM KNOW THEY HAVE WON A PRIZE
                        $mail->AddAddress($row["EmailAddress"]);
                        $mail->Send();
                        $mail->ClearAttachments();
                        $mail->ClearAllRecipients();
                        $mail->ClearReplyTos();    
                    }
                }



            $db->commit();
            $db->null;
        }

    catch (PDOException $e)
        {
            $db->rollback();
            echo $e->getMessage();

            exit;
        }
?>

"who have enrolled more than one member"... shouldn't your having clause be "HAVING COUNT(AGENT) > 1"? — Stacey Richards
– Stacey Richards, Commented Oct 21, 2010 at 12:15
@Stacey Richards - you are correct. I just wrote in the code incorrectly above while typing out my question but is correct on my site (rules are actually different there too). Regardless, it doesn't necessarily affect the issue I have. — JM4
– JM4, Commented Oct 21, 2010 at 18:07

Stacey Richards · Accepted Answer · 2010-10-21 12:26:20Z

2

This gets rid of the second foreach and (hopefully - I haven't tested) gets the agent's email address in the one select:

...
$sql = $db->query("SELECT tablename.AGENT AGENT, agent.EMAIL EMAIL, 
                   FROM tablename, agent 
                   WHERE enroll_date > DATE_SUB(CURDATE(),INTERVAL 1 MONTH) AND tablename.AGENT = agent.AGENT 
                   GROUP BY tablename.AGENT 
                   HAVING COUNT(tablename.AGENT) > 1 
                   ORDER BY enroll_date ASC");             
$result = $sql->fetchALL(PDO::FETCH_ASSOC);
foreach($result as $row)
{
  $sql2 = "UPDATE agent_prizes 
           SET prize=?, method=? 
           WHERE AGENT = '{$row["AGENT"]}'";
...

answered Oct 21, 2010 at 12:26

Stacey Richards

6,6127 gold badges40 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

PHP PDO fetch results

SCRIPT:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

SCRIPT:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related