User specific emails based on criteria

[morphosis]

I hope this makes sense.

 

I have some very basic tables(see below)

 

  tblSubscribers (sid,name,email,)

  tblCriteria (cid,sid,businesstype,businesslocation)

  tblBusinesses (bid,businessadded,businesstype,businesslocation,businessdescription)

 

1st query

I need to check every 7 days for any new businesses which have been added to the database and check which match the subscribers criteria and display the emailaddress with the number of business matched

 

example:

[email protected] (2)

[email protected](7)

[email protected](4)

 

2nd Query

I then need to send an email to each of the emailaddresses with the body of the email containing the businesses associated with that email.

 

(similar to a jobsite newsletter when you get updates on jobs in your area and jobtype and salary you specified)

 

Hope this makes sense?

 

I'm just not strong enough with PHP and MySQL to get it working. I'm happy to start from scratch if there is a better way.

 

I have managed to loop through the records but I just send one email with one business and then again to the same email with onther business! 

 

Any thoughts or help will be most gratefully received. example code would help or a link to a article which would give me the ground work.

 

Many thanks

[morphosis]

OK..

 

I finally got it to do what I want it to do! only problem is I think it's not very efficient.

 

Could this be more efficient or it actual OK?

 

Any thoughts?

 

$NoDays = "7"; 

$query  = "SELECT emailaddress, tblsubscribers.subscriber_id, bussaleID AS bid, COUNT(bussaleID) AS businesscount FROM tblcriteria, tblsubscribers, tblbusinesses WHERE tblcriteria.subscriber_id = tblsubscribers.subscriber_id AND tblcriteria.location = tblbusinesses.buslocation AND tblcriteria.bustype = tblbusinesses.bussector AND DATE_SUB(CURDATE(),INTERVAL ".$NoDays." DAY) <= tblbusinesses.busadded GROUP BY emailaddress";
mysql_query($query) or die('emailaddress query failed: ' . mysql_error());

$result = mysql_query($query);
$numofrows = mysql_num_rows($result); 						
	for($i = 0; $i < $numofrows; $i++) {
		$row = mysql_fetch_array($result); 
		$records = $row['BusinessCount'];
			if ($numofrows =='0'){ 
				echo "Currently no records match your search.<br />"; 
			}else{ 	
				echo "( <strong> ".$records." </strong>) ". $row['emailaddress']. " <br>";
				$query1 = "SELECT buslocation, bussector, bussaleID, COUNT(*) AS repetitions FROM tblcriteria, tblsubscribers, tblbusinesses WHERE tblcriteria.subscriber_id = '". $row[subscriber_id]. "' AND tblcriteria.location = tblbusinesses.BusLocation AND tblcriteria.bustype = tblbusinesses.bussector AND DATE_SUB(CURDATE(),INTERVAL ".$NoDays." DAY) <= tblbusinesses.busadded GROUP BY bussaleID HAVING repetitions > 1";
					mysql_query($query1) or die('businesses query  failed: ' . mysql_error());
					$result1 = mysql_query($query1);
					$numofrows1 = mysql_num_rows($result1);
						for($k = 0; $k < $numofrows1; $k++) {
							$row1 = mysql_fetch_array($result1);
								echo " ---- {$row1['bussaleID']}";
								echo " - {$row1['bussector']}  ";
								echo " - {$row1['buslocation']} ";
								echo "<br>";

						}	 
			}	 																			
	} 

 

and this gives me the results like this

( 1 )[email protected] 
     12 print Yorkshire 

( 1 )[email protected] 
     12 print Yorkshire 

( 4 )[email protected] 
     8 print North West England 
     9 print North West England 
    10 print North West England 
    14 print North West England