Using foreach with a nested while statement

Beginners - Learning PHP
#1

I have a form that allows input of up to 3 holidays ($date1, $date2, $date3) and corresponding hours to appy to each holiday ($hrs1, $hrs2, $hrs3). I want for each holiday-hours pair to insert a record for each employee into a table. This requires iteration through the holiday-hours array and the employee table to get all the info to insert. The problem is that it only inserts records for the first holiday-hours pair. Here’s my current code:
$date1 = $_POST[“date1”];
$date2 = $_POST[“date2”];
$date3 = $_POST[“date3”];
$hrs1 = $_POST[“hrs1”];
$hrs2 = $_POST[“hrs2”];
$hrs3 = $_POST[“hrs3”];
$right_now = date(“D M d, Y G:i”);

$tsql1 = "SELECT rec_id, first_name, last_name FROM employees";
$stmt1 = sqlsrv_query($conn, $tsql1);

$holidays = array($date1 => $hrs1, $date2 => $hrs2, $date3 => $hrs3);

foreach($holidays as $date => $hrs) {
	while( $row = sqlsrv_fetch_array($stmt1, SQLSRV_FETCH_ASSOC)) {
		$tsql2 = "INSERT INTO dbo.time_off 
				(emp_rec_id, 
				emp_first_name, 
				emp_last_name,
				leave_type, 
				req_date, 
				approved, 
				total_req_time, 
				last_modified)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?)";  
		$params2 = array($row['rec_id'], $row['first_name'], $row['last_name'], 'Holiday', $date, 'Yes', $hrs, $right_now);  
		$stmt2 = sqlsrv_query( $conn, $tsql2, $params2 );
	}
}
sqlsrv_free_stmt($stmt1);  
sqlsrv_free_stmt($stmt2);  
sqlsrv_close($conn);

Can you tell me why this only inserts one record for each employee when two or three holidays are entered?

#2

Once you have fetched the result set from a query, there’s no more data to fetch. The while(){} loop is false after you have fetched the last row from the result set.

To do what you are asking, you should just reverse the order of the loops.

You should also use a prepared query for the insert query to save the time needed to parse and plan the execution of the query.

#3

You guys are awesome - wish I had asked this earlier! I would think that once the “while” loop was false, it would fall back to the “foreach” statement to start the “while” again. Thank you!

#4

That is what it does. The foreach(){} is looping three times, but the while(){} loop is always false after the first pass through the foreach(){} loop. It would require being able to perform a data seek back to the 1st row in the result set or executing the SELECT query again to be able to loop over the result set again. Both of these take more code and more processing/resources. Keep It Simple (KISS.) Use the simplest logic/syntax that accomplishes a task.

If you did have some need to do it the original way, you should just fetch the result set from the select query into a php array variable, then just loop over the php array variable as many times as needed.

1 Like
#5

Ahhh… okay - that makes so much more sense to me now. Swapping those two loops did the trick and was very easy to do. Just shows how much I have to learn. Thanks again!

Sponsor our Newsletter | Privacy Policy | Terms of Service