Nested while/for loops maybe?

komlos

I have the below code that I run daily, and which sends notification mail to members if there is an ad in the "ad" table that matches specific criteria saved in "notice" table. Works like the feature on job sites.

My problem is that this code sends every matching ad in separate mail and this way the same member gets multiple emails.

What do I need to modify in this code to make it send one email per member with the ads listed in it like:

Subject of ad 1
Link for ad 1

Subject of ad 2
Link for ad 2

Subject of ad 3
Link for ad 3

.
.
.

etc.

I think maybe nested while or for loops are the solution...

You can see a demo here:
www.mnyknt.hu/alert

I appreciate any help, thanks


$oneday = mysql_query("SELECT * FROM $ads_tbl, $notice_tbl WHERE 
						$ads_tbl.cat_id = $notice_tbl.cat_id
					AND	$ads_tbl.biztype = $notice_tbl.biztype
						ORDER BY $notice_tbl.email") or error(mysql_error());

while($rowday = mysql_fetch_array($oneday))
{
	$ad_id = $rowday['ad_id'];
	$subject = $rowday['subject'];
	$descript = $rowday['descript'];
	$cat_id = $rowday['cat_id'];
	$territory = $rowday['territory'];

	// email headers

	$from = "$ADMIN_EMAIL";
	$to = $rowday['email'];
	$mailsubject = "New ad listing from $ADVT_NAME";

	// the content of the email just for testing

	echo $subject."<br>";
	echo $ADVT_URL."/ads/view.php?id=".$ad_id."<br>";

	echo $to."<br><br>";

	// disabled for testing
	//sentMail($from, $to, $mailsubject, $body);  
}

mtimdog

Well, you're wanting to order by email address, right?....this is not tested

//btw, I would change the * to be more specific for the tables so
//it executes much faster.

$oneday = mysql_query("SELECT * FROM $ads_tbl, $notice_tbl WHERE 
                        $ads_tbl.cat_id = $notice_tbl.cat_id
                    AND    $ads_tbl.biztype = $notice_tbl.biztype
                        ORDER BY $notice_tbl.email") or error(mysql_error());

$first='true';
$to='';
$from = "$ADMIN_EMAIL"; //take these out because they never change.
$mailsubject = "New ad listing(s) from $ADVT_NAME";
while($rowday = mysql_fetch_array($oneday))
{
$ad_id = $rowday['ad_id'];
    $subject = $rowday['subject'];
    $descript = $rowday['descript'];
    $cat_id = $rowday['cat_id'];
    $territory = $rowday['territory'];

if ($to!= $rowday['email']){
     if ($first == 'true') {
          $first='false';
     }else{//send the previous mail
          //sentMail($from, $to, $mailsubject, $body);  

     }
   $to=$rowday['email'];
   $body="$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>"; 



}else{//only need to add the other ads

  $body.="<br>$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>"; 

}//end else 

    // the content of the email (so far) just for testing

    echo $subject."<br>";
    echo $ADVT_URL."/ads/view.php?id=".$ad_id."<br>";

    echo $to."<br><br>";

    // disabled for testing

}
//send the last mail
//sentMail($from, $to, $mailsubject, $body);

komlos

Works wonderfuly!!! Thanks! However, I had to disable my original echoes and put some others in the code for testing. Am I doing it right?. See below:

I don't understand how it works. Could you explain the logic of your solution? (Why do we need to use the sentmail function twice? How does this previous mail and last mail approach work?)


$oneday = mysql_query("SELECT * FROM $ads_tbl, $notice_tbl WHERE  

                        $ads_tbl.cat_id = $notice_tbl.cat_id 
                    AND    $ads_tbl.biztype = $notice_tbl.biztype 
                        ORDER BY $notice_tbl.email") or error(mysql_error()); 

$first='true'; 
$to=''; 
$from = "$ADMIN_EMAIL"; //take these out because they never change. 
$mailsubject = "New ad listing(s) from $ADVT_NAME"; 
while($rowday = mysql_fetch_array($oneday)) 
{ 
$ad_id = $rowday['ad_id']; 
    $subject = $rowday['subject']; 
    $descript = $rowday['descript']; 
    $cat_id = $rowday['cat_id']; 
    $territory = $rowday['territory']; 

if ($to!= $rowday['email']){ 
     if ($first == 'true') { 
          $first='false'; 
     }else{//send the previous mail 
          //sentMail($from, $to, $mailsubject, $body);

	 echo $to."<br>";
	 echo $body."<br><br>";

 } 
   $to=$rowday['email']; 
   $body="$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>";  



}else{//only need to add the other ads 

  $body.="<br>$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>";  

}//end else 

    /* the content of the email (so far) just for testing 

   echo $subject."<br>"; 
    echo $ADVT_URL."/ads/view.php?id=".$ad_id."<br>"; 

    echo $to."<br><br>"; 
   */

} 
//send the last mail 
//sentMail($from, $to, $mailsubject, $body);

echo $to."<br>";
echo $body."<br><br>";

Thanks for this nice solution!!

mtimdog

sorry for no comments...here it goes

$oneday = mysql_query("SELECT * FROM $ads_tbl, $notice_tbl WHERE  

                        $ads_tbl.cat_id = $notice_tbl.cat_id 
                    AND    $ads_tbl.biztype = $notice_tbl.biztype 
                        ORDER BY $notice_tbl.email") or error(mysql_error()); 

$first='true'; //is it the first time in loop
$to='';  //who to send to 
$from = "$ADMIN_EMAIL"; //take these out because they never change.
$mailsubject = "New ad listing(s) from $ADVT_NAME"; 
while($rowday = mysql_fetch_array($oneday)) 
{ 
$ad_id = $rowday['ad_id']; 
    $subject = $rowday['subject']; 
    $descript = $rowday['descript']; 
    $cat_id = $rowday['cat_id']; 
    $territory = $rowday['territory']; 
//if the email doesn't equal to, then it's a new person
if ($to!= $rowday['email']){ 
     if ($first == 'true') {//if it's the first time in the loop(has to be a new person),
//then do nothing, and set $first so it doesn't catch anymore 
          $first='false'; 
     }else{//send out the mail to the previous person
          //sentMail($from, $to, $mailsubject, $body);

     echo $to."<br>";
     echo $body."<br><br>";

 } 
//since it's a new person, set the $to and the original $body.
   $to=$rowday['email']; 
   $body="$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>";  



}else{//only need to add the other ads

  $body.="<br>$subject<br>$ADVT_URL/ads/view.php?id=$ad_id<br>";  

}//end else

    /* the content of the email (so far) just for testing

   echo $subject."<br>";
    echo $ADVT_URL."/ads/view.php?id=".$ad_id."<br>";

    echo $to."<br><br>";
   */

} 
//send the  mail for the last person (because it doesn't loop back since he/shes last)
//sentMail($from, $to, $mailsubject, $body);

echo $to."<br>";
echo $body."<br><br>";

Hope this makes some sense, and your echos were correctly placed🙂

komlos

I removed "order by $notice_tbl.email" from sql select and now script doesn't work, why? (gives strange result) Btw, I'm not insisting on ordering result it was like that only for testig purposes.

Thanks :-)

mtimdog

i was ordering the result so that the rows in the query would have the same addresses continuous (otherwise, it don't work). It would probly work with group by/having as well btw.