Nested Loop

karenn1 · 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

titan21 · 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.

PFMaBiSmAd · 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);
}
}

cyberRobot · August 19, 2011

@PFMaBiSmAd - you beat me to it; I was going to something very similar. Your code is more complete though.

karenn1 · 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

karenn1 · 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

PFMaBiSmAd · 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.

cunoodle2 · 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]

karenn1 · August 22, 2011

@PFMaBiSmAd, thanks again! I really appreciate your help!!!!! I'll give the WHERE clause a try.

Sign In

Nested Loop

Recommended Posts

karenn1

Link to comment

Share on other sites

titan21

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

karenn1

Link to comment

Share on other sites

karenn1

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

cunoodle2

Link to comment

Share on other sites

karenn1

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information