while / foreach - problem with nesting ?

[ejwf]

I am querying the database to find firstly a list of company names, then information that corresponds to that company and send an e-mail to the correct person in that company that contains the right information for the relevant company, but I just cannot make it work.
the code I have is as follows

$today = date('Y-m-d');

$query = "SELECT CRef FROM tblDate WHERE Open = '$today'";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_array($result))
$CRef[] = $row["CRef"];

 foreach ($CRef as $ref)
  {
  $query = "SELECT CoName FROM tblCon WHERE CId = '".$ref."'";
  $result1 = mysql_query($query) or die(mysql_error());

	if ($row1 = mysql_fetch_array($result1))
	 {
	 $CoName = $row1["CoName"];

		$query = "SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND CRef = '".$ref."'";
		$result2 = mysql_query($query) or die(mysql_error());

			while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
			{ 

			$CEmail = $row2["CEmail"];

			$query = "SELECT CName FROM tblUser WHERE CEmail = '$CEmail' AND CRef = '".$ref."'";
			$result6 = mysql_query($query) 	or die (mysql_error());
			$row6 = mysql_fetch_array($result6);
			$Name = $row6["CName"];

			$query = "SELECT SectionRef FROM tblUser WHERE CName = '$Name' AND CRef = '".$ref."'";
			$result7 = mysql_query($query) 	or die (mysql_error());
			$row7 = mysql_fetch_array($result7);
			$SectionRef = $row7["SectionRef"];

			$query = "SELECT SectionName FROM tblSection WHERE SectionId = '$SectionRef' AND CRef = '".$ref."'";
			$result8 = mysql_query($query) 	or die (mysql_error());
			$row8 = mysql_fetch_array($result8);
			$SectionName = $row8["SectionName"];

			$query = "SELECT ClientRef FROM tblUser WHERE CEmail = '$CEmail' AND CRef = '".$ref."'";
			$result9 = mysql_query($query) 	or die (mysql_error());
			$row9 = mysql_fetch_array($result9);
			$ClientRef = $row9["ClientRef"];

			$query = "SELECT CoName FROM tblClient WHERE ClientId = '$ClientRef' AND CRef = '".$ref."'";
			$result10 = mysql_query($query) 	or die (mysql_error());
			$row10 = mysql_fetch_array($result10);
			$ClientName = $row10["CoName"];


					$body1 = "THIS IS AN AUTOMATICALLY GENEREATED MESSAGE FROM $CoName

					Hello $Name of $ClientName,

					blah blah blah blah information from  $SectionName blah blah blah

						Yours sincerely,
					$CoName						";

		mail ("$CEmail", "subject", "$body1", "FROM:[e-mail]");
			} //close while	
	} //close if
} //close foreach

I am expecting this code to generate -
one CRef / CoName
two different ClientName - Rubber Soles and Yellow Submarine
3 different CEmail / Name for each Client -
Rubber Soles has John Lennon / Bob Builder / Andy Pandy
Yellow Submarine has George Harrison / Ringo Starr / Joe Bloggs
2 Different SectionName for each ClientName

for example John Lennon and Bob builder both work for rubber soles but John Lennon at the london business park section, and bob builder works at the leicester section.

the code above generates 3 emails to Andy Pandy and one to Ringo Starr - nothing is sent to the others.
Having said that the information contained in the e-mail is correct !

What I am struggling to correct is the way to 'nest' the foreach / while loops so that I get 6 e-mails, one to each of the $Names each with the correct/relevant information in the e-mail.

Can anyone help - I have been playing around with this for 3 days now and cannot get it right !

thanks

[ahundiak]

I'd suggest fireing up mysql or phpdbadmin and reducing the number of your queries.

For example:
SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND CRef = $ref
SELECT CName FROM tblUser WHERE CEmail = '$CEmail' AND CRef
SELECT SectionRef FROM tblUser WHERE CName = '$Name' AND CRef =$ref
SELECT ClientRef FROM tblUser WHERE CEmail =...

Can be reduced to
SELECT CEmail,CName,SectionRef,ClientRef
FROM tblUser
WHERE AuthorityLevel >= 3 AND CRef = $ref

You can also join these two together:
SELECT CRef FROM tblDate WHERE Open = '$today'
SELECT CoName FROM tblCon WHERE CId = '".$ref."'"

SELECT CRef,CoName
FROM tblDate,tblCon
WHERE Open = '$today' AND
tblDate.CRef = tblCon.CId

I think once you get rid of most of those foreach's and queries then the problem will be easy to track down.

[ejwf]

Not sure how or why this worked but thank you !

have changed the query for
SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND CRef = $ref
SELECT CName FROM tblUser WHERE CEmail = '$CEmail' AND CRef
SELECT SectionRef FROM tblUser WHERE CName = '$Name' AND CRef =$ref
SELECT ClientRef FROM tblUser WHERE CEmail =...

to

			$query = "SELECT CEmail, CName, SectionRef, ClientRef FROM tblUser 
			WHERE tblUser.AuthorityLevel >= 3 AND tblUser.CRef = '".$ref."'";
			$result2 = mysql_query($query) or die(mysql_error());

			while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
			{ 

			$CEmail = $row2["CEmail"];
			$Name = $row2["CName"];
			$SectionRef = $row2["SectionRef"];
			$ClientRef = $row2["ClientRef"];

I get all the e-mails as required all with the correct information

THANK YOU !