karenn1 Posted August 19, 2011 Share Posted August 19, 2011 Hello everybody, I hope someone can help me. I've got a database table with subscriber info linked to various tenders. The current layout of the table is as follows (this is dummy data): Subscriber Category Email Tender Info Dummy Company 1 Advertisting email@email.com Tender 1 Dummy Company 1 Advertisting email@email.com Tender 2 Dummy Company 1 Advertisting email@email.com Tender 3 Dummy Company 1 Advertisting email@email.com Tender 4 Dummy Company 2 Marketing email2@email.com Tender 5 Dummy Company 2 Marketing email2@email.com Tender 6 What I'm trying to do, is to send Company 1 an email with the info for all 4 tenders. If I do a normal loop, it sends 4 emails, each mail having the info only for one tender. Here's my code: //Select subscriber and email address, grouped to only have one entry per sub $subs = "SELECT subscriber, email FROM tenders_temp GROUP BY subscriber"; $query = mysql_query($subs); while ($subsrs = mysql_fetch_array($query)) { //Select all tenders info $tender = "SELECT * FROM tenders_temp WHERE subscriber = '".$subsrs['subscriber']."'"; $restender = mysql_query($tender); //Send email $msg = "<html><body> <table width='100%' border='0' cellspacing='0' cellpadding='10'>"; while ($rstend = mysql_fetch_array($restender)) { $msg .= " <tr> <td valign='top'><strong>SUBSCRIBER:</strong></td> <td colspan='3'>".$rstend['subscriber']."</td> </tr> <tr> <td valign='top'><strong>CATEGORY:</strong></td> <td colspan='3'>".$rstend['tender_interests']."</td> </tr> <tr> <td width='16%' valign='top'><strong>REFERENCE: </strong></td> <td width='34%'>".$rstend['reference']."</td> <td width='15%'><strong>CLOSING:</strong></td> <td width='35%'>".$rstend['closedate']." ".$rstend['closetime']."</td> </tr> <tr> <td valign='top'><strong>DESCRIPTION:</strong></td> <td colspan='3'>".$rstend['summary']." ".$rstend['description']." ".$rstend['detail']."</td> </tr> <tr> <td valign='top'><strong>CONTACT:</strong></td> <td colspan='3'>".$rstend['organization']."; ".$rstend['contact']."; Phone: ".$rstend['dial_code']." ".$rstend['telno']."; Email: ".$rstend['email']."</td> </tr> <tr> <td valign='top'><strong>DOCUMENTATION:</strong></td> <td colspan='3'>".$rstend['documentation']."</td> </tr> <tr> <td> </td> <td> </td> <td colspan='2'> </td> </tr> <tr> <td> </td> <td> </td> <td colspan='2'> </td> </tr>"; } $msg .= " </table> </body></html>"; $headers = "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type: text/html; charset=iso-8859-1" . "\r\n"; $headers .= "From: info@tenders24.com\r\n"; $headers .= "Reply-To: info@tenders24.com\r\n"; $headers .= "Return-Path: info@tenders24.com\r\n"; $headers .= "Organization: Tenders24\r\n"; $headers .= "X-Priority: 3\r\n"; mail($subsrs['email'], "Tenders24", $msg, $headers); } Looking at this code, in theory it should work. The first query groups the entries to have one email address, then the second loop, takes each entry and loops the number of tenders inside the email. I'm getting an error with this tho. Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource Can anybody please help?? Thanks in advance! Karen Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/ Share on other sites More sharing options...
titan21 Posted August 19, 2011 Share Posted August 19, 2011 Can you change the line: $query = mysql_query($subs); to $query = mysql_query($subs) or die(mysql_error()); and let us know what you get? Looks like whatever is being queried is not valid so this should help. Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1259367 Share on other sites More sharing options...
PFMaBiSmAd Posted August 19, 2011 Share Posted August 19, 2011 You would also almost never use a query inside of a loop. The following (untested) logic should work - <?php // your database connection/selection code here... $query = "SELECT * FROM tenders_temp ORDER BY subscriber"; // get the rows you want in the order that you want them if(!$result = mysql_query($query)){ // query failed, handle the error here... trigger_error("Query failed: $query, Error: " . mysql_error()); echo "A database error occurred and this page cannot be displayed!"; } else { // query worked, test if it matched any rows if(!mysql_num_rows($result)){ // no rows were matched echo "There were no matching rows"; } else { // matched at leat one row, process the data here... $headers = "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type: text/html; charset=iso-8859-1" . "\r\n"; $headers .= "From: info@tenders24.com\r\n"; $headers .= "Reply-To: info@tenders24.com\r\n"; $headers .= "Return-Path: info@tenders24.com\r\n"; $headers .= "Organization: Tenders24\r\n"; $headers .= "X-Priority: 3\r\n"; $last_subscriber = null; // remember the last subscriber's email, initialize to a value that will never exist in the data while($row = mysql_fetch_assoc($result)){ // test for a change in the subscriber if($last_subscriber != $row['email']){ // the subscriber changed (or is the first one) if($last_subscriber != null){ // not the start of the first one, close out the previous section $msg .= " </table> </body></html>"; mail($last_subscriber, "Tenders24", $msg, $headers); } // start a new section $msg = "<html><body> <table width='100%' border='0' cellspacing='0' cellpadding='10'>"; $last_subscriber = $row['email']; // remember the new subscriber } // process each row of data here... $msg .= " <tr> <td valign='top'><strong>SUBSCRIBER:</strong></td> <td colspan='3'>".$row['subscriber']."</td> </tr> <tr> <td valign='top'><strong>CATEGORY:</strong></td> <td colspan='3'>".$row['tender_interests']."</td> </tr> <tr> <td width='16%' valign='top'><strong>REFERENCE: </strong></td> <td width='34%'>".$row['reference']."</td> <td width='15%'><strong>CLOSING:</strong></td> <td width='35%'>".$row['closedate']." ".$row['closetime']."</td> </tr> <tr> <td valign='top'><strong>DESCRIPTION:</strong></td> <td colspan='3'>".$row['summary']." ".$row['description']." ".$row['detail']."</td> </tr> <tr> <td valign='top'><strong>CONTACT:</strong></td> <td colspan='3'>".$row['organization']."; ".$row['contact']."; Phone: ".$row['dial_code']." ".$row['telno']."; Email: ".$row['email']."</td> </tr> <tr> <td valign='top'><strong>DOCUMENTATION:</strong></td> <td colspan='3'>".$row['documentation']."</td> </tr> <tr> <td> </td> <td> </td> <td colspan='2'> </td> </tr> <tr> <td> </td> <td> </td> <td colspan='2'> </td> </tr>"; } // close out the last section $msg .= " </table> </body></html>"; mail($last_subscriber, "Tenders24", $msg, $headers); } } Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1259385 Share on other sites More sharing options...
cyberRobot Posted August 19, 2011 Share Posted August 19, 2011 @PFMaBiSmAd - you beat me to it; I was going to something very similar. Your code is more complete though. Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1259388 Share on other sites More sharing options...
karenn1 Posted August 22, 2011 Author Share Posted August 22, 2011 @PFMaBiSmAd - you are an absolute ROCK STAR!!! The code works perfectly! Thank you SO SO much! I'm thankful there are still people like you around. Blessings! Karen Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1260427 Share on other sites More sharing options...
karenn1 Posted August 22, 2011 Author Share Posted August 22, 2011 @PFMaBiSmAd, can I ask another question? Everything is working fine. Only thing I encountered is when there is no info for a tender, the table is blank. ie: Subscriber Category Email Tender Info Dummy Company 1 Advertisting email@email.com Tender 1 Dummy Company 1 Promotions email@email.com No current tenders for this category Dummy Company 1 Construction email@email.com No current tenders for this category So, in your coding, it's looping through the rows, but the values are blank. Any way around this? Karen Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1260436 Share on other sites More sharing options...
PFMaBiSmAd Posted August 22, 2011 Share Posted August 22, 2011 The point of the query that is being executed is to - get the rows you want in the order that you want them. You would add a WHERE clause to the query so that only the rows that have tender information are matched. Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1260470 Share on other sites More sharing options...
cunoodle2 Posted August 22, 2011 Share Posted August 22, 2011 We should be able to "LIKE" PFMaBiSmAd comments. Amazing skills. OH.. see my attachment. The cab and PFMaBiSmad are both sooo 1337. [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1260486 Share on other sites More sharing options...
karenn1 Posted August 22, 2011 Author Share Posted August 22, 2011 @PFMaBiSmAd, thanks again! I really appreciate your help!!!!! I'll give the WHERE clause a try. Quote Link to comment https://forums.phpfreaks.com/topic/245181-nested-loop/#findComment-1260501 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.