Iteratre through rows, update and send email

[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.

<?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
	
	
?>

[mac_gyver]

you have far too much code and variables, making it difficult to see the forest for the trees, a lot of which isn't even used (and i don't mean the commented out debugging code.)

 

you should write just the code and variables you need for any step. it would also help if you first define and write a general comment at the start of each step stating what the step is supposed to accomplish.

 

the following (untested) is a first-pass simplification of your code, with the unneeded bits removed (the comments i put/left in the code are the general comments that help define what the code is doing) -

// query for booster dates within the next 30 days
$sql = "SELECT serviceID 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 . ']');
}

while($row = $result->fetch_array()){

    // update the email30 field for any matching rows (there might be previous/existing rows as well.)
    $sql = "UPDATE service SET email30 = 30 WHERE serviceID = {$row['serviceID']}";

    if(!$con->query($sql)){
        die('There was an error running the query [' . $con->error . ']');
    }
}

// get all rows with email30 set (existing and new ones)
$sql = "SELECT patientID, boosterDate 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()){
    list($patientID,$boosterDate) = $row;
    // get patient info
    $sql = "SELECT clientID, name FROM patientInformation WHERE patientID = $patientID}";

    if(!$resultb = $con->query($sql)){
        die('There was an error running the query [' . $con->error . ']');
    }

    list($clientID,$name) = $resultb->fetch_array();
    // get client info
    $sql = "SELECT firstName, lastName, email FROM clientInformation WHERE clientID = $clientID}";

    if(!$resultb = $con->query($sql)){
        die('There was an error running the query [' . $con->error . ']');
    }
    
    list($firstName,$lastName,$email) = $resultb->fetch_array();

    // $email, $firstName, $lastName - clientInformation
    // $name - patientInformation
    // $boosterDate - service
    $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\r\n" .
        "$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);
}

next, a few advanced tips -

 

1) you don't need to select data and loop through it, in order to update it based on a condition.

 

2) each simple query you run takes far longer to communicate from php to the database server than what it takes to actually run on the database server, so you should avoid running queries in loops and running multiple queries that operate on related data.  you need to learn to write JOIN'ed query statements (there's a ton of information in the mysql documentation and on the Internet)

 

what your code (untested) could be simplified to look like -

// update data within the next 30 days
$sql = "UPDATE service SET email30 = 30 WHERE boosterDate BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY";

if(!$con->query($sql)){
    die('There was an error running the query [' . $con->error . ']');
}

// get all rows with email30 set (existing and new ones)
$sql = "SELECT s.boosterDate, p.name, c.firstName, c.lastName, c.email
    FROM service s
    JOIN patientInformation p USING(patientID)
    JOIN clientInformation c USING(clientID)
    WHERE s.email30 = 30";

if(!$result = $con->query($sql)){
    die('There was an error running the query [' . $con->error . ']');
}

while($row = $result->fetch_array()){
    list($boosterDate, $name, $firstname, $lastName, $email) = $row;
    $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\r\n" .
        "$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);
}

Edited November 13, 2013 by mac_gyver

[ndjustin20]

Thank you very much for your replay.  In the past I've always used simple queries to achieve what I needed.  This one was by far the most complex and using JOIN makes things soooooooo MUCH SIMPLER!!!!!   I'm going to definitely go in that direction from now on as it makes life so much easier. 

 

 

Thanks again for the help!!!