StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP PDO fetch results
text
Body
copied!<p>I have the following script which runs fine but I think </p> <p>a) it is ugly;</p> <p>b) it doesn't do what I need after the script is run.</p> <p>Utlimately, the script should work as follows:</p> <p>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.</p> <p>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). </p> <p>To accomplish this, my script currently runs back to back foreach loops (which I hate) in order to break out the necessary field data.</p> <p>Any suggestions for improving the process are appreciated.</p> <h2>SCRIPT:</h2> <pre><code><?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 = "user@domain.com"; $mail->Password = ""; $mail->SetFrom("user@domain.com"); $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; } ?> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload