Page 1 of 1

Iteratre through rows, update and send email

Posted: Tue Nov 12, 2013 8:18 pm
by ndjustin20
I am having an issue with iterating through rows, updating a field, then sending an email depending on the date. I can get the first row to send an email too but I can't figure out why I cannot get the other rows to send an email to. I can show that there are three rows returned yet I cannot send three emails. The code is as follows and thank you in advance for your help.

Code: Select all

<?php
		
	


		for($numOfRows = 1; $numOfRows > 0;){		
		
		$sql = "SELECT * FROM service WHERE boosterDate BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY";

	if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
		$numOfRows = $result->num_rows;
		//%result->store_result();
		$numOfRows = var_dump($numOfRows);
		//echo $numOfRows;
	
	//if($numOfRows > 0){
		
		//var_dump($numOfRows);
		
	while($row = $result->fetch_array()){
			//Get all the rows and store them in an array	
		$firstQueryRows[] = $row;
						
	}
	
	foreach($firstQueryRows as $row){
		
		//do a new query with $row
		$serviceID = $row['serviceID'];
		$patientID = $row['patientID'];
		$serviceName = $row['serviceName'];
		$price = $row['price'];
		$quantity = $row['quantity'];
		$boosterDate = $row['boosterDate'];
		$totalPrice = $row['totalPrice'];
		$rabiesTagNumber = $row['rabiesTagNumber'];
		$microChipNumber = $row['microChipNumber'];
		$rabiesDose = $row['rabiesDose'];
		$rabiesVacType = $row['rabiesVacType'];
		
		$rabiesVacExpDate = $row['rabiesVacExpDate'];
		$rabiesSerialNumber = $row['rabiesSerialNumber'];
		$rabiesManufacturer = $row['rabiesManufacturer'];
		$rabiesVacDate = $row['rabiesVacDate'];
		$rabiesVacDueBy = $row['rabiesVacDueBy'];
		$licenseNumber = $row['licenseNumber'];
		$isThisARabiesCert = $row['isThisARabiesCert'];
		$vacDate = $row['vacDate'];
		$email30 = $row['email30'];
		$email60 = $row['email60'];
		$email90 = $row['email90'];		
		//echo $serviceID . "  ";
		$sql = "UPDATE service SET email30 = 30 WHERE serviceID = $serviceID";

		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
				
		}//End Foreach
			
		
		
		//echo $patientID;
		
		
		$sql = "SELECT * FROM service WHERE email30 = 30";
		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
		while($row = $result->fetch_array()){
			//Get all the rows and store them in an array	
		$firstQueryRows[] = $row;
		
		$patientID = $row['patientID'];
		//echo $patientID;
						
	}
	
		foreach($firstQueryRows as $row){
			
			
			
			$sql = "SELECT * FROM patientInformation WHERE patientID = $patientID";
		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}		
		
		}
		
		while($row = $result->fetch_array()){
			$firstQueryRows[] = $row;
			$clientID = $row['clientID'];
			$maleFemale = $row['maleFemale'];
			$species = $row['species'];
			$ageYears = $row['ageYears'];
			$ageMonths = $row['ageMonths'];
			$weight = $row['weight'];
			$dogBreed = $row['dogBreed'];
			$name = $row['name'];
			$catBreed = $row['catBreed'];
			$colors = $row['colors'];
			$illnessLast30Days = $row['illnessLast30Days'];
			$explainIllness = $row['explainIllness'];
			
			
			//echo $clientID;	
			
		}
		
		foreach($firstQueryRows as $row){
			
			$sql = "SELECT * FROM clientInformation WHERE clientID = $clientID";
		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
		}
		
		while($row = $result->fetch_array()){
			
			$firstName = $row['firstName'];
			$lastName = $row['lastName'];
			$address = $row['address'];
			$city = $row['city'];
			$zip = $row['zip'];
			$phone = $row['phone'];
			$cell = $row['cell'];
			$state = $row['state'];
			$email = $row['email'];
			
			
			
			
		}
		
		$to = $email;
		$subject = "Just a friendly reminder from the Shot Spot";
		
		
		$message = "Greetings " . $firstName . " " . $lastName . "\r\n" . "\r\n" . "\r\n"  .
			"Thank you for choosing the Shot Spot for all of your animals healthcare needs.  This is a friendly reminder that  
								" . $name . " is ready for their booster on " . $boosterDate . "\r\n" . "\r\n" . "\r\n" . 
								"Thank you for choosing the Shot Spot";
								
		
		$headers = 'From: info@txshotspot.com';

		mail($to, $subject, $message, $headers);

		
	
	}//end for loop
	
	
?>

Re: Iteratre through rows, update and send email

Posted: Mon Dec 30, 2013 9:29 am
by akuji36
Hello

I believe your main problem is your initial select statement: SELECT * FROM service WHERE boosterDate BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY.

Try to determine timespan a little clearer.

Take a look at this:

You need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today's records.

In this case, you'll need to use NOW instead:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()